﻿'/*
' * FAHWatch7 DATA Copyright Marvin Westmaas ( mtm ) 
' *
' * This program is free software; you can redistribute it and/or
' * modify it under the terms of the GNU General Public License
' * as published by the Free Software Foundation; version 2
' * of the License. See the included file GPLv2.TXT.
' * 
' * This program is distributed in the hope that it will be useful,
' * but WITHOUT ANY WARRANTY; without even the implied warranty of
' * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
' * GNU General Public License for more details.
' * 
' * You should have received a copy of the GNU General Public License
' * along with this program; if not, write to the Free Software
' * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
' */
'/*
'

Imports System.IO
Imports System.Data
Imports System.Data.SQLite
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.Drawing
Imports System.Text
Imports System.Threading

Public Class Data
    Implements IDisposable
    Private strCon As String = ""
    Private dbName As String = ""
    Private Const strDTFormat As String = "dd/MM/yyyy-H:mm:ss"
    Private sib As String = "','"
    Private sqlErr As SQLiteErrorCode
    Public Class clsDB
        Implements IDisposable
        Public Class clsDBInstance
            Implements IDisposable
#Region "Events"
            Public Event ConnectionStateChange(ID As Int32, OldState As ConnectionState, NewState As ConnectionState)
            Public Event HandleExc(RaisedExeption As Exception, TheErr As ErrObject, ID As Int32, exInfo As String)
            Public Event cmdDisposed(ID As Int32)
            Public Event conDisposed(ID As Int32)
            Public Event IsDisposed(ID As Int32)
#End Region
#Region "Declares"
            Private dtNow As DateTime = #1/1/2000#
            Private b_cmdDisposed As Boolean, b_conDisposed As Boolean
            Private i_id As Int32 = -1
            Private bAllowDispose As Boolean = True
            Private WithEvents con As SQLiteConnection
            Private trans As SQLiteTransaction
            Private WithEvents cmd As New SQLiteCommand
            Private WithEvents rdr As SQLiteDataReader
            Private sqlException As SQLiteErrorCode = SQLiteErrorCode.Ok
            Property TimeOut As TimeSpan = TimeSpan.FromSeconds(5)
            Property strConnection As String = String.Empty
#End Region
#Region "Properties"
            Public Property dbMaster As Boolean
                Get
                    Return bAllowDispose
                End Get
                Set(value As Boolean)
                    bAllowDispose = Not value
                End Set
            End Property
            Public ReadOnly Property ID As Int32
                Get
                    Return i_id
                End Get
            End Property
            Public ReadOnly Property conState As ConnectionState
                Get
                    Return con.State
                End Get
            End Property
            Public ReadOnly Property LastException As SQLiteErrorCode
                Get
                    Return sqlException
                End Get
            End Property
            Public ReadOnly Property GetSQLCommand As SQLiteCommand
                Get
                    Try
                        cmd = con.CreateCommand
                        Return CType(cmd.Clone, SQLiteCommand)
                    Catch ex As Exception

                    Finally

                    End Try
                End Get
            End Property
            <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")> Public ReadOnly Property ExecuteNonQuery(Sql As String, Optional sqlType As System.Data.CommandType = CommandType.Text) As SQLiteErrorCode
                Get
                    Try
                        If con.State = ConnectionState.Open Then
                            Try
                                cmd = con.CreateCommand
                            Catch Sqlex As Exception
                                'new instance, or try to run on the same instance?
                                Try
                                    Select Case cmd.Transaction.IsolationLevel
                                        Case IsolationLevel.ReadCommitted

                                        Case IsolationLevel.Chaos

                                        Case IsolationLevel.ReadUncommitted

                                        Case IsolationLevel.RepeatableRead

                                        Case IsolationLevel.Serializable

                                        Case IsolationLevel.Snapshot

                                        Case IsolationLevel.Unspecified

                                    End Select
                                    cmd.CommandType = sqlType
                                    cmd.CommandText = Sql
                                    Return CType(cmd.ExecuteNonQuery, SQLiteErrorCode)
                                Catch Sqlex2 As Exception

                                End Try

                            End Try
                            cmd.CommandType = sqlType
                            cmd.CommandText = Sql
                            Return CType(cmd.ExecuteNonQuery, SQLiteErrorCode)
                        Else
                            WriteLog("Can't execute sql against a closed connection", eSeverity.Important)
                            Return SQLiteErrorCode.CantOpen
                        End If
                    Catch ex As Exception

                    End Try
                End Get
            End Property
            Public ReadOnly Property ExecuteCommand(Command As SQLiteCommand) As SQLiteErrorCode
                Get
                    Command.Connection = con
                    Return CType(cmd.ExecuteNonQuery, SQLiteErrorCode)
                End Get
            End Property
            Public ReadOnly Property dataReader(Sql As String) As SQLiteDataReader
                Get
                    Try
                        If Not IsNothing(cmd.Transaction) Then
                            WriteDebug("check")
                        ElseIf con.State = ConnectionState.Closed Then
                            If con.ConnectionString <> "" Then
                                Try
                                    con.Open()
                                    GoTo IsOpen
                                Catch sqlEx As Exception
                                    'New instance?
                                End Try
                            End If
                        ElseIf con.State = ConnectionState.Open Then
IsOpen:
                            Try
                                cmd = con.CreateCommand
                                GoTo SetCMD
                            Catch sqlEx As Exception
                                'New instance?
                            End Try
                        End If
SetCMD:
                        Try
                            cmd.CommandText = Sql
                        Catch sqlEx As Exception

                        End Try
                        Try
                            Return cmd.ExecuteReader
                        Catch sqlEx As Exception

                        End Try
                    Catch Ex As Exception

                    End Try
                End Get
            End Property
#End Region
#Region "connection state handling"
            Private Sub con_Commit(sender As Object, e As System.Data.SQLite.CommitEventArgs) Handles con.Commit
                If DebugOutput Then WriteDebug("sqliteconnection " & ID.ToString & " commited a transaction - " & IsNothing(cmd.Transaction).ToString)
            End Sub
            Private Sub con_Disposed(sender As Object, e As System.EventArgs) Handles con.Disposed
                If DebugOutput Then WriteDebug("sqliteconnection " & ID.ToString & " has been disposed")
                b_conDisposed = True
            End Sub
            Private Sub con_RollBack(sender As Object, e As System.EventArgs) Handles con.RollBack
                If DebugOutput Then WriteDebug("sqliteconnection " & ID.ToString & " rolled back a transaction")
            End Sub
            Private Sub con_Update(sender As Object, e As System.Data.SQLite.UpdateEventArgs) Handles con.Update
                If DebugOutput Then WriteDebug("sqliteconnection " & ID.ToString & " performed " & e.Event.ToString & " " & e.Table.ToString & " row " & e.RowId.ToString)
            End Sub
            Private Sub con_StateChange(sender As Object, e As System.Data.StateChangeEventArgs) Handles con.StateChange
                If DebugOutput Then WriteDebug("sqliteconnection " & ID.ToString & " state changed from " & e.OriginalState.ToString & " to " & e.CurrentState.ToString)
                RaiseEvent ConnectionStateChange(ID, e.OriginalState, e.CurrentState)
            End Sub
            Public Sub TryOpenCon(Optional tsWait As TimeSpan = Nothing, Optional _con As SQLiteConnection = Nothing)
                Try
                    If Not IsNothing(con) Then
                        WriteLog("Passed on  a cloned sql connection " & _con.ConnectionString & " " & con.State.ToString)
                        con = _con
                    Else
                        con = New SQLiteConnection
                    End If
                    Dim tmpTS As TimeSpan = TimeOut
                    If IsNothing(tsWait) Then
                        WriteDebug("tryopencon " & ID.ToString & " called, dtNow=" & dtNow.ToString & " TimeOut=" & TimeOut.ToString)
                    Else
                        WriteDebug("tryopencon " & ID.ToString & " called, dtNow=" & dtNow.ToString & " TimeOut=" & TimeOut.ToString & " tsWait=" & tsWait.ToString)
                    End If
                    If b_conDisposed Then
                        WriteLog("Con id:" & ID.ToString & " was disposed, creating new one")
                        CreateCon()
                        GoTo TryOpen
                    ElseIf con.State = ConnectionState.Open Then
                        WriteLog("sqliteconnection id:" & ID.ToString & " is already open", eSeverity.Debug)
                        Exit Sub
                    ElseIf con.State = ConnectionState.Broken Then
                        TryCloseCon()
                        If con.State = ConnectionState.Broken Then
                            'try to dispose and recreate
                            dtNow = DateTime.Now
                            If Not IsNothing(tsWait) Then
                                TimeOut = tsWait
                            End If
                            con.Dispose()
                            While Not DateTime.Now.Subtract(dtNow) > TimeOut
                                Application.DoEvents()
                                If IsNothing(con) Or b_conDisposed Then
                                    WriteDebug("Con disposed")
                                    Exit While
                                End If
                            End While
                            dtNow = #1/1/2000#
                            If Not IsNothing(tsWait) Then
                                TimeOut = tmpTS
                            End If
                            con = New SQLiteConnection(strConnection)
                            GoTo TryOpen
                        ElseIf con.State = ConnectionState.Closed Then
                            GoTo TryOpen
                        End If
                    Else
TryOpen:
                        dtNow = DateTime.Now
                        If Not IsNothing(tsWait) Then
                            TimeOut = tsWait
                        End If
                        con.ConnectionString = strConnection
                        con.Open()
                        While Not DateTime.Now.Subtract(dtNow) > TimeOut
                            Application.DoEvents()
                            If con.State = ConnectionState.Open Then
                                WriteDebug("Con opened")
                                Exit While
                            End If
                        End While
                        dtNow = #1/1/2000#
                        If Not IsNothing(tsWait) Then
                            TimeOut = tmpTS
                        End If
                        If con.State = ConnectionState.Open Then
                            WriteLog("Created sqlitecommand for instance " & ID.ToString)
                            cmd = con.CreateCommand
                        Else
                            WriteLog("Can't open the connection for id:" & ID.ToString & " " & con.State.ToString, eSeverity.Debug)
                        End If
                    End If
                Catch ex As Exception
                    RaiseEvent HandleExc(ex, Err, ID, "tryopencon-" & con.ConnectionString & "-" & con.State.ToString)
                End Try
            End Sub
            Public Sub TryCloseCon(Optional tsWait As TimeSpan = Nothing)
                Try
                    If IsNothing(tsWait) Then
                        WriteDebug("tryclosecon " & ID.ToString & " called, dtNow=" & dtNow.ToString & " TimeOut=" & TimeOut.ToString & " " & con.State.ToString)
                    Else
                        WriteDebug("tryclosecon " & ID.ToString & " called, dtNow=" & dtNow.ToString & " TimeOut=" & TimeOut.ToString & " tsWait=" & tsWait.ToString & " " & con.State.ToString)
                    End If
                    If con.State <> ConnectionState.Open Then
                        WriteLog("connection was not open, aborting close")
                        Exit Sub
                    End If
                    con.Close()
                    dtNow = DateTime.Now
                    Dim tmpTS As TimeSpan = TimeOut
                    If Not IsNothing(tsWait) Then
                        TimeOut = tsWait
                    End If
                    While Not DateTime.Now.Subtract(dtNow) > TimeOut
                        Application.DoEvents()
                        If con.State = ConnectionState.Closed Then
                            WriteDebug("Connection closed")
                            Exit While
                        End If
                    End While
                    dtNow = #1/1/2000#
                    If Not IsNothing(tsWait) Then
                        TimeOut = tmpTS
                    End If
                    If Not con.State = ConnectionState.Closed Then
                        WriteLog("Can't close an sqliteconnection: " & strConnection & " - " & con.State.ToString, eSeverity.Critical)
                    End If
                    tmpTS = Nothing
                Catch ex As Exception
                    RaiseEvent HandleExc(ex, Err, ID, "tryclosecon-" & con.ConnectionString & "-" & con.State.ToString)
                    'WriteError(ex.Message, Err)
                End Try
            End Sub
            Private Sub CreateCon()
                Try
                    WriteDebug("Creating connection for " & ID.ToString & " - " & strConnection)
                    con = New SQLiteConnection(strConnection)
                    b_conDisposed = False
                Catch ex As Exception
                    WriteError(ex.Message, Err)
                End Try
            End Sub
#End Region
#Region "Transaction handling"
            Private bTransAction As Boolean = False
            Public ReadOnly Property TransactionInProgress As Boolean
                Get
                    Try
                        If IsNothing(trans) Then Return False
                        Return Not IsNothing(trans.Connection)
                    Catch ex As Exception
                        Return False
                    End Try
                End Get
            End Property
            Public Sub BeginTransaction()
                Try
                    If TransactionInProgress Then
                        WriteDebug("transaction " & ID.ToString & " already running")
                    Else
                        WriteDebug("createtransaction:" & ID.ToString)
                        trans = con.BeginTransaction()
                        bTransAction = True
                    End If
                Catch ex As Exception
                    WriteError(ex.Message, Err)
                End Try
            End Sub
            Public Sub EndTransaction()
                Try
                    WriteDebug("endtransaction:" & ID.ToString)
                    If IsNothing(trans.Connection) Then
                        WriteDebug("transaction " & ID.ToString & " doesn't exist")
                    ElseIf Not bTransAction Then
                        WriteDebug("transaction " & ID.ToString & " doesn't exist")
                    Else
                        Try
                            trans.Commit()
                            Application.DoEvents()
                            bTransAction = Not IsNothing(trans.Connection)
                        Catch ex As Exception

                        End Try
                    End If
                Catch ex As Exception
                    WriteError(ex.Message, Err)
                End Try
            End Sub
#End Region
#Region "sqlitecommand handling"
            Private Sub cmd_Disposed(sender As Object, e As System.EventArgs) Handles cmd.Disposed
                b_cmdDisposed = True
                RaiseEvent cmdDisposed(ID)
            End Sub
#End Region
            ' sqlitecom property for external access
            Public Function CreateInstance(ID As Int32, Optional strCon As String = Nothing, Optional tsTimeOut As TimeSpan = Nothing) As Boolean
                Dim bRet As Boolean = False
                Try
                    i_id = ID
                    If Not IsNothing(tsTimeOut) AndAlso tsTimeOut.TotalMilliseconds > 0 Then TimeOut = tsTimeOut
                    If Not IsNothing(strCon) Then strConnection = strCon
                    If IsNothing(strConnection) Or strConnection = String.Empty Then
                        strConnection = sqdata.dbPool.connectionString
                    End If
                    'CreateCon()
                    WriteDebug("Creating connection for " & ID.ToString & " - " & strConnection)
                    con = New SQLiteConnection(strConnection)
                    bRet = True
                Catch ex As Exception
                    WriteError(ex.Message, Err)
                End Try
                Return bRet
            End Function
            Public Sub ClearError()
                sqlException = 0
            End Sub
            Public Sub New(Optional ID As Int32 = -1)
                i_id = ID
            End Sub
            Public Sub Dispose() Implements System.IDisposable.Dispose
                If ID = 0 And Not bAllowDispose Then
                    WriteDebug("Preventing dispose of dbMaster")
                    Exit Sub
                End If
                WriteDebug("dbInstance:" & ID.ToString & " disposing")
                Me.Finalize()
            End Sub
            Protected Overrides Sub Finalize()
                RaiseEvent IsDisposed(ID)
                WriteDebug("dbInstance:" & ID.ToString & " finalizing")
                MyBase.Finalize()
            End Sub
        End Class
        Private dInstances As New Dictionary(Of Int32, clsDBInstance)
#Region "Instance event handlers"
        Private Sub _cmdDisposed(ID As Object)
            WriteDebug("dbInstance " & ID.ToString & " disposed the sqlitecommand")
        End Sub
        Private Sub _conDisposed(ID As Object)
            WriteDebug("dbInstance " & ID.ToString & " disposed the sqliteconnection")
        End Sub
        Private Sub _ConnectionStateChange(ID As Integer, OldState As System.Data.ConnectionState, NewState As System.Data.ConnectionState)
            WriteDebug("dbInstance " & ID.ToString & " changed state from " & OldState.ToString & " to " & NewState.ToString)
        End Sub
        Private Sub _HandleExc(RaisedExeption As System.Exception, TheErr As Microsoft.VisualBasic.ErrObject, ID As Integer, exInfo As String)
            WriteError("dbInstance " & ID.ToString & " :" & RaisedExeption.Message, Err)
        End Sub
        Private Sub FreeInstance(ID As Int32)
            Try
                WriteDebug("dbInstance:" & ID.ToString & " has been disposed, removing from dictionary")
                dInstances.Remove(ID)
                WriteDebug("-" & dInstances.Count & " - " & dInstances.ContainsKey(ID))
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
        End Sub
#End Region
        Private Function GetFreeID() As Int32
            Dim iRet As Int32 = -1
            Try
                For xInt As Int32 = 0 To Int32.MaxValue
                    If Not dInstances.ContainsKey(xInt) Then
                        iRet = xInt
                        Exit For
                    End If
                Next
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return iRet
        End Function
        Public ReadOnly Property dbInstance(Optional ID As Int32 = 0, Optional conString As String = Nothing, Optional tsTimeOut As TimeSpan = Nothing) As clsDBInstance
            Get
                Try
                    If ID = 0 Then
                        If Not Master.TransactionInProgress Then
                            Return Master
                        Else
                            WriteLog("Main database instance called which was bussy with a transaction")
                            ID = -1
                            GoTo ForceNew
                        End If
                        Return Master
                    ElseIf ID = -1 Then
ForceNew:
                        Dim nInstance As New clsDBInstance()
                        AddHandler nInstance.IsDisposed, AddressOf FreeInstance
                        If IsNothing(conString) Then conString = connectionString
                        If IsNothing(tsTimeOut) Then TimeOut = tsTimeOut
                        If nInstance.CreateInstance(GetFreeID, conString, tsTimeOut) Then
                            dInstances.Add(nInstance.ID, nInstance)
                            WriteDebug("Created dbInstance " & ID.ToString)
                            Return nInstance
                        Else
                            WriteDebug("Instance failed to initialize")
                            Return Nothing
                        End If
                    Else
                        If dInstances.ContainsKey(ID) Then
                            Return dInstances(ID)
                        Else
                            WriteDebug("error")
                            Return Nothing
                        End If
                    End If
                Catch ex As Exception

                End Try
            End Get
        End Property
        Private connectionString As String = String.Empty, dbFile As String = String.Empty, bIsNew As Boolean = False, TimeOut As TimeSpan = TimeSpan.FromSeconds(5)
        Public ReadOnly Property databaseFile As String
            Get
                Return dbFile
            End Get
        End Property
        Public ReadOnly Property IsNew As Boolean
            Get
                Return bIsNew
            End Get
        End Property
        Public Property TimeOutInterval As TimeSpan
            Get
                Return TimeOut
            End Get
            Set(value As TimeSpan)
                TimeOut = value
                'maybe don't use it globally
                For Each dbI As clsDBInstance In dInstances.Values.ToList
                    dbI.TimeOut = value
                Next
            End Set
        End Property
        Private WithEvents Master As clsDBInstance
        Public Sub Init(Optional dbName As String = Nothing, Optional Pragma As String = Nothing, Optional tsTimeOut As TimeSpan = Nothing)
            Try
                If Not IsNothing(dbName) Then
                    dbFile = dbName
                Else

                End If
                If Not IsNothing(tsTimeOut) Then TimeOut = tsTimeOut
                If Not dbFile.IndexOf("\") = -1 Then
                    dbFile = dbFile.Replace("\", "/")
                End If
                If Not My.Computer.FileSystem.FileExists(dbName) Then
                    bIsNew = True
                    connectionString = "Data source=" & dbFile & ";New=True"
                    If IsNothing(Pragma) Then
                        connectionString &= ";Compress=True;Synchronous=on"
                    Else
                        connectionString &= ";" & Pragma
                    End If
                Else
                    connectionString = "Data source=" & dbFile
                    If IsNothing(Pragma) Then
                        connectionString &= ";Compress=True;Autocommit=off"
                    Else
                        connectionString &= ";" & Pragma
                    End If
                End If
                Master = New clsDBInstance(0)
                Master.dbMaster = True
                'Master.con = New SQLiteConnection(connectionString)
                Master.strConnection = connectionString
                Try
                    Master.TryOpenCon()
                Catch ex As Exception
                    WriteLog("Can't initialize database access", eSeverity.Important)
                    WriteError(ex.Message, Err)
                    Exit Try
                End Try
                AddHandler Master.conDisposed, AddressOf _conDisposed
                AddHandler Master.cmdDisposed, AddressOf _cmdDisposed
                AddHandler Master.ConnectionStateChange, AddressOf _ConnectionStateChange
                AddHandler Master.IsDisposed, AddressOf FreeInstance
                dInstances.Add(0, Master)
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
        End Sub
        Public ReadOnly Property dbMaster As clsDBInstance
            Get
                Return Master
            End Get
        End Property
        Public ReadOnly Property IsClosed As Boolean
            Get
                Return CBool(dInstances.Count = 0)
            End Get
        End Property
        Sub close()
            Try
                If dInstances.Count > 0 Then
                    'Allow close master
                    dbMaster.dbMaster = False
                    Dim nL(0 To dInstances.Count - 1) As clsDBInstance
                    dInstances.Values.CopyTo(nL, 0)
                    For Each dInstance As clsDBInstance In nL
                        'dInstance = Nothing
                        dInstance.TryCloseCon()
                        Application.DoEvents()
                        dInstance.Dispose()
                    Next
                    Dim dtNow As DateTime = DateTime.Now
                    Do
                        Application.DoEvents()
                    Loop Until DateTime.Now.Subtract(dtNow).TotalSeconds >= 5 Or dInstances.Count = 0
                    If dInstances.Count > 0 Then
                        WriteLog("Couldn't close all database connections", eSeverity.Important)
                    End If
                End If
                Try
                    If My.Computer.FileSystem.FileExists(Application.StartupPath & "\" & My.Application.Info.AssemblyName & "_old.db") Then
                        My.Computer.FileSystem.DeleteFile(Application.StartupPath & "\" & My.Application.Info.AssemblyName & "_old.db")
                    End If
                Catch ex As Exception

                End Try
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
        End Sub
#Region "IDisposable Support"
        Private disposedValue As Boolean ' To detect redundant calls

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: dispose managed state (managed objects).
                    For Each DictionaryEntry In dInstances

                        DictionaryEntry.Value.Dispose()
                    Next
                End If

                ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
                ' TODO: set large fields to null.
            End If
            Me.disposedValue = True
        End Sub

        ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
        'Protected Overrides Sub Finalize()
        '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub

        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region
    End Class
    Public dbPool As New clsDB
#Region "Initialize database access / convert older databases"
    Public Function Init(ByVal DataRoot As String) As Boolean
        Try
            If Not DataRoot.LastIndexOf("\") = DataRoot.Length - 1 Then
                DataRoot &= "\"
            End If
            'Check for backup db, application exit should remove it, offer chanche of restoring
            If My.Computer.FileSystem.FileExists(DataRoot & My.Application.Info.AssemblyName & "_old.db") Then
                Dim rVal As MsgBoxResult = MsgBox("A backup database file is present, do you want to restore it?", CType(MsgBoxStyle.OkCancel + MsgBoxStyle.Exclamation + MsgBoxStyle.MsgBoxSetForeground, MsgBoxStyle), "Restore backup?")
                If rVal = MsgBoxResult.Ok Then
                    Try
                        My.Computer.FileSystem.DeleteFile(DataRoot & My.Application.Info.AssemblyName & ".db")
                    Catch ex As Exception

                    End Try
                    Try
                        My.Computer.FileSystem.CopyFile(DataRoot & My.Application.Info.AssemblyName & "_old.db", My.Application.Info.AssemblyName & ".db")
                    Catch ex As Exception

                    End Try
                End If
            Else
                If My.Computer.FileSystem.FileExists(DataRoot & My.Application.Info.AssemblyName & ".db") Then
                    Try
                        My.Computer.FileSystem.CopyFile(DataRoot & My.Application.Info.AssemblyName & ".db", My.Application.Info.AssemblyName & "_old.db")
                    Catch ex As Exception
                        WriteError(ex.Message, Err)
                    End Try
                End If
            End If


            dbPool.Init(DataRoot & My.Application.Info.AssemblyName & ".db", "Compress=True;Synchronous=off", New TimeSpan(0, 0, 5))
            Try
                Dim dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        Dim rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name like '%ProjectInfo_%'")
                        Dim bR As Boolean = rdr.HasRows
                        _pNames.Clear()
                        While rdr.Read
                            Dim strTmp As String = rdr.Item(0).ToString
                            strTmp = strTmp.Replace("ProjectInfo_", "")
                            Dim dtTemp As DateTime = DateTime.FromBinary(CLng(strTmp.Replace("p", ".").Replace("c", ","))).ToLocalTime
                            _pNames.Add("ProjectInfo_" & dtTemp.ToString("dd/MM/yyyy-H:mm:ss"))
                        End While
                        rdr.Close()
                        rdr = Nothing
                        If bR Then
                            Dim pI As New clsProjectInfo
                            WriteLog("Older database structure detected, merging project info tables.")
                            Bussy.ShowForm("Merging project info tables...")
                            pI.Projects = MergeProjectInfos()
                            pI = Update_ProjectInfo(pI)
                            _pNames.Clear()
                            Bussy.SetMessage("Cleaning database..")
                            PurgeDB()
                            Bussy.CloseForm()
                            WriteLog("Converting finished!")
                        End If
                        Return True
                    Else
                        WriteDebug("Can't open the database specified, " & dbName & " - " & strCon & " - ")
                        Return False
                    End If
                End With
            Catch ex As Exception
                WriteError(ex.Message, Err)
                Return False
            End Try
        Catch ex As Exception
            WriteDebug("data.init - failed to init dbPool")
            WriteError(ex.Message, Err)
            Return False
        End Try
    End Function
    Public Sub PurgeDB()
        Try
            Dim dbInst As clsDB.clsDBInstance = dbPool.dbInstance
            With dbInst
                If .conState = ConnectionState.Open Then
                    .BeginTransaction()
                    WriteDebug("Purging database leaving only project information")
                    Dim lNames As New List(Of String)
                    Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE NOT name like '%ProjectInfo%'")
                        While rdr.Read
                            lNames.Add(CStr(rdr.Item(0)))
                        End While
                    End Using
                    For Each Table In lNames
                        'cmd = sqlitecon.createcommand
                        sqlErr = .ExecuteNonQuery("DROP TABLE " & Table)
                    Next
                    'VACUUM DB
                    .EndTransaction()
                    If .TransactionInProgress Then
                        WriteDebug("Database vacuum")
                        sqlErr = .ExecuteNonQuery("VACUUM")
                    End If
                Else
                    WriteDebug("Can't access the database, there should be more errors")
                End If
            End With
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
    End Sub
#End Region
#Region "Project info"
    Public ReadOnly Property HasProjectInfo As Boolean
        Get
            Dim bRet As Boolean = False
            Try
                WriteDebug("Checking the database for projectinformation")
                Dim dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        '.BeginTransaction()
                        Dim bHas As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='ProjectInfo'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            'close cmd/sqlitecon
                            Exit Try
                        End If
                        WriteDebug("Table found, checking records")
                        Dim lPnames As New List(Of String)
                        Try
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ProjectInfo'")
                                While rdr.Read
                                    lPnames.Add(rdr.Item("ProjectNumber").ToString)
                                    'Ignore for now
                                    bRet = True
                                    Exit While
                                End While
                            End Using
                        Catch ex As Exception
                            WriteError(ex.Message, Err)
                            bHas = False
                        End Try
                    Else
                        WriteLog("Can't access the database table, there should be more errors")
                    End If
                    '.EndTransaction()
                End With
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return bRet
        End Get
    End Property
    Public Function Update_ProjectInfo(ByVal ProjectInfo As clsProjectInfo) As clsProjectInfo
        Dim rInf As clsProjectInfo = ProjectInfo
        Try
            Dim dbInst As clsDB.clsDBInstance = dbPool.dbInstance
            With dbInst
                Dim bHas As Boolean
                Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='ProjectInfo'")
                    bHas = rdr.HasRows
                End Using
                If Not bHas Then
                    WriteLog("Creating projectinfo table")
                    Try
                        sqlErr = .ExecuteNonQuery("CREATE TABLE 'ProjectInfo' (ProjectNumber TEXT, ServerIP TEXT, WuName TEXT, NumberOfAtoms TEXT, PrefferedDays TEXT, FinalDeadline TEXT, Credit TEXT, Frames TEXT, Code TEXT, Contact TEXT, kFactor TEXT, UnitType TEXT, Description TEXT, dtSummary DATETIME)")

                    Catch ex As Exception
                        WriteError(ex.Message, Err)
                        WriteDebug("Returning original projectinfo without updating the table")
                        Return ProjectInfo
                    End Try
                End If
                Dim bCommit As Boolean = Not .TransactionInProgress
                If Not .TransactionInProgress Then
                    .BeginTransaction()
                End If
                For xIndex As Short = 1 To CShort(ProjectInfo.Projects.ProjectCount - 1)
                    Dim nP As clsProjectInfo.sProject.clsProject = ProjectInfo.Projects.Project(xIndex)
                    If IsNothing(nP.ProjectNumber) Then
                        WriteLog("Empty project in collection", eSeverity.Debug)
                        Exit For
                    End If
                    Dim dtComp As DateTime = DateTime.MinValue
                    Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ProjectInfo' WHERE ProjectNumber='" & nP.ProjectNumber & "'")
                        bHas = rdr.HasRows
                        Try
                            If Not IsDBNull(rdr.Item("dtSummary")) Then
                                dtComp = CDate(rdr.Item("dtSummary"))
                            End If
                        Catch ex As Exception
                            dtComp = DateTime.Now
                        End Try
                    End Using
                    'Check if new entries overwrite older one's
                    If bHas And dtComp > nP.dtSummary Then
                        WriteLog("Updating project information for #" & nP.ProjectNumber, eSeverity.Debug)
                        sqlErr = .ExecuteNonQuery("UPDATE 'ProjectInfo' SET ServerIP='" & nP.ServerIP & "', WuName='" & nP.WUName & "', NumberOfAtoms='" & nP.NumberOfAtoms & "', PrefferedDays='" & nP.PreferredDays & "', FinalDeadline='" & nP.FinalDeadline & "', Credit='" & nP.Credit & "', Frames='" & nP.Frames & "', Code='" & nP.Code & "', Contact='" & nP.Contact & "', kFactor='" & nP.kFactor & "', UnitType='" & nP.ProjectType & "', Description='" & nP.Description & "', dtSummary='" & nP.dtSummary.ToString("s") & "' WHERE ProjectNumber='" & nP.ProjectNumber & "'")
                    ElseIf Not bHas Then
                        WriteLog("Adding project information for #" & nP.ProjectNumber, eSeverity.Debug)
                        sqlErr = .ExecuteNonQuery("INSERT INTO 'ProjectInfo' (ProjectNumber, ServerIP, WuName, NumberOfAtoms, PrefferedDays, FinalDeadline, Credit, Frames, Code, Contact, kFactor, UnitType, Description, dtSummary) VALUES ('" & nP.ProjectNumber & "','" & nP.ServerIP & "','" & nP.WUName & "','" & nP.NumberOfAtoms & "','" & nP.PreferredDays & "','" & nP.FinalDeadline & "','" & nP.Credit & "','" & nP.Frames & "','" & nP.Code & "','" & nP.Contact & "','" & nP.kFactor & "','" & nP.ProjectType.ToString & "','" & nP.Description & "','" & nP.dtSummary.ToString("s") & "')")
                    End If
                Next
                If bCommit Then .EndTransaction()
                rInf.Projects = ReadProjectInfo
            End With
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return rInf
    End Function
    Public ReadOnly Property ReadProjectInfo As clsProjectInfo.sProject
        Get
            Dim pInfo As New clsProjectInfo.sProject
            Try
                If Not HasProjectInfo Then Return pInfo
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Dim bCommit As Boolean = Not .TransactionInProgress
                            If Not .TransactionInProgress Then
                                .BeginTransaction()
                            End If
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ProjectInfo'")
                                While rdr.Read
                                    Dim nPr As New clsProjectInfo.sProject.clsProject
                                    With nPr
                                        .ProjectNumber = rdr.Item("ProjectNumber").ToString
                                        Try
                                            .dtSummary = CDate(rdr.Item("dtSummary"))
                                        Catch ex As Exception
                                            .dtSummary = DateTime.Now
                                        End Try
                                        .Code = rdr.Item("Code").ToString '0 
                                        .Contact = rdr.Item("Contact").ToString '1
                                        .Credit = rdr.Item("Credit").ToString '2
                                        .Description = rdr.Item("Description").ToString '3
                                        If .Description.ToUpper.Substring(0, 4) = "HTTP" Then
                                            'WriteDebug("Checking with FAHControl.db for project #" & .ProjectNumber & " description: " & GetFAHControlProjectDescription(.ProjectNumber).ToString)
                                        End If
                                        .FinalDeadline = rdr.Item("FinalDeadline").ToString '4
                                        .Frames = rdr.Item("Frames").ToString '5
                                        .kFactor = rdr.Item("kFactor").ToString '6
                                        .NumberOfAtoms = rdr.Item("NumberOfAtoms").ToString '7
                                        .PreferredDays = rdr.Item("PrefferedDays").ToString
                                        If rdr.Item("UnitType").ToString = "Beta" Then
                                            .ProjectType = clsProjectInfo.sProject.eProjectType.Beta
                                        ElseIf rdr.Item("UnitType").ToString = "Advanced" Then
                                            .ProjectType = clsProjectInfo.sProject.eProjectType.Advanced
                                        ElseIf rdr.Item("UnitType").ToString = "Regular" Then
                                            .ProjectType = clsProjectInfo.sProject.eProjectType.Regular
                                        End If
                                        .ServerIP = rdr.Item("ServerIP").ToString
                                        .WUName = rdr.Item("WuName").ToString
                                    End With
                                    If DebugOutput Then WriteDebug("Added #" & nPr.ProjectNumber & " to known projects")
                                    pInfo.AddProject(nPr)
                                End While
                                If bCommit Then .EndTransaction()
                            End Using
                        Else
                            WriteLog("Can't access the database, there should be more errors", eSeverity.Debug)
                        End If
                    End With
                End Using
                Return pInfo
            Catch ex As Exception
                WriteError(ex.Message, Err)
                Return pInfo
            End Try
        End Get
    End Property
    'Depreciated but used for backwards compatibility 
#Region "Depreciated functions left intact to import older psummary data"
    Private _pNames As New List(Of String)
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2202:Do not dispose objects multiple times")> Private Function MergeProjectInfos() As clsProjectInfo.sProject
        Dim nProjects As New clsProjectInfo.sProject
        Try
            'Reversing should keep all changed wu's to their last values
            For Each pName In ProjectInfo_names
                Dim pInf As clsProjectInfo.sProject = Read_Projects(pName)
                For Each Project As clsProjectInfo.sProject.clsProject In pInf.colProjects
                    If Not nProjects.KnownProject(Project.ProjectNumber) Then
                        nProjects.AddProject(Project)
                    ElseIf Project.dtSummary > nProjects.Project(Project.ProjectNumber).dtSummary Then
                        nProjects.UpdateProject(Project)
                    End If
                Next
            Next
            Return nProjects
        Catch ex As Exception
            WriteError(ex.Message, Err)
            Return nProjects
        End Try
    End Function
    Private Function Read_Projects(ByVal Name As String) As clsProjectInfo.sProject
        'Depreciated
        Dim nProjects As New clsProjectInfo.sProject
        Try
            Dim pName As String = ""
            Dim dtTemp As DateTime
            Dim dtPI As DateTime = DateTime.ParseExact(Name.Replace("ProjectInfo_", ""), "dd/MM/yyyy-H:mm:ss", Nothing)
            Dim dbInst As clsDB.clsDBInstance = dbPool.dbInstance
            With dbInst
                If .conState = ConnectionState.Open Then
                    Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name like '%ProjectInfo_%'")
                        Dim bR As Boolean = rdr.HasRows
                        While rdr.HasRows
                            If Not rdr.Read Then Exit While
                            Dim strTmp As String = rdr.Item(0).ToString
                            strTmp = strTmp.Replace("ProjectInfo_", "")
                            dtTemp = DateTime.FromBinary(CLng(strTmp.Replace("p", ".").Replace("c", ","))).ToLocalTime
                            If dtTemp.ToString("dd/MM/yyyy-H:mm:ss") = dtPI.ToString("dd/MM/yyyy-H:mm:ss") Then
                                pName = "ProjectInfo_" & strTmp
                                Exit While
                            End If
                        End While
                    End Using
                    If pName = "" Then
                        Return nProjects
                    End If
                    Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM '" & pName & "'")
                        Dim nPr As clsProjectInfo.sProject.clsProject
                        While rdr.HasRows
                            nPr = New clsProjectInfo.sProject.clsProject
                            With nPr
                                .dtSummary = dtTemp
                                .Code = rdr.Item("Code").ToString '0 
                                .Contact = rdr.Item("Contact").ToString '1
                                .Credit = rdr.Item("Credit").ToString '2
                                .Description = rdr.Item("Description").ToString '3
                                .FinalDeadline = rdr.Item("FinalDeadline").ToString '4
                                .Frames = rdr.Item("Frames").ToString '5
                                .kFactor = rdr.Item("kFactor").ToString '6
                                .NumberOfAtoms = rdr.Item("NumberOfAtoms").ToString '7
                                .PreferredDays = rdr.Item("PrefferedDays").ToString
                                .ProjectNumber = rdr.Item("ProjectNumber").ToString
                                If rdr.Item("UnitType").ToString = "Beta" Then
                                    .ProjectType = clsProjectInfo.sProject.eProjectType.Beta
                                ElseIf rdr.Item("UnitType").ToString = "Advanced" Then
                                    .ProjectType = clsProjectInfo.sProject.eProjectType.Advanced
                                ElseIf rdr.Item("UnitType").ToString = "Regular" Then
                                    .ProjectType = clsProjectInfo.sProject.eProjectType.Regular
                                End If
                                .ServerIP = rdr.Item("ServerIP").ToString
                                .WUName = rdr.Item("WuName").ToString
                            End With
                            nProjects.AddProject(nPr)
                            If Not rdr.Read Then Exit While
                        End While
                    End Using
                    WriteDebug("Read table " & pName)
                Else
                    WriteDebug("Reading table " & pName & " failed")
                End If
            End With
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return nProjects ' will be empty if function fails 
    End Function
    Private ReadOnly Property ProjectInfo_names As List(Of String)
        Get
            Return _pNames
        End Get
    End Property
#End Region
#Region "FAHControl.db project description retrieval"
    Public Function GetFAHControlProjectDescriptions() As Boolean
        Dim bRet As Boolean = True, bSucces As Boolean = False
        Try
            'Should use transaction but this should work
            Dim dFile As String = ClientConfig.Configuration.DataLocation & "\FAHControl.db"
            If Not My.Computer.FileSystem.FileExists(dFile) Then Exit Try
            Dim dicDescriptions As New Dictionary(Of String, String)
            Using dbInstFC As clsDB.clsDBInstance = dbPool.dbInstance(-1, "Data source=" & dFile & ";mode=ro")
                With dbInstFC
                    .TryOpenCon()
                    Application.DoEvents()
                    If .conState = ConnectionState.Open Then
                        Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM projects")
                            While rdr.Read
                                dicDescriptions.Add(CStr(rdr.Item(0)), CStr(rdr.Item(1)))
                            End While
                        End Using
                        If dicDescriptions.Count = 0 Then Exit Try
                    Else
                        WriteLog("Can't access the FAHControl database, there could be more errors", eSeverity.Debug)
                        Exit Try
                    End If
                End With
            End Using

            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        For Each DictionaryEntry In dicDescriptions
                            Using rdr As SQLiteDataReader = .dataReader("SELECT Description FROM ProjectInfo WHERE ProjectNumber='" & DictionaryEntry.Key & "' AND NOT Description='" & FormatSQLString(DictionaryEntry.Value, True) & "'")
                                If rdr.HasRows Then
                                    Try
                                        sqlErr = .ExecuteNonQuery("UPDATE ProjectInfo SET Description='" & FormatSQLString(CStr(rdr.Item("description"))) & "' WHERE ProjectNumber='" & CStr(rdr.Item("id")) & "'")
                                        bSucces = (sqlErr = 0)
                                    Catch exSql As SQLiteException
                                        WriteLog("Updating project info description failed with the following error:", eSeverity.Important)
                                        WriteError(exSql.Message, Err)
                                        bSucces = False
                                    End Try
                                End If
                            End Using
                        Next
                    Else
                        WriteLog("Database writing failed", eSeverity.Debug)
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteLog("WARNING: FAHControl.db format not as expected, can not read project descriptions", eSeverity.Debug)
            bRet = False
        End Try
        Return bRet AndAlso bSucces
    End Function
    Public Function GetFAHControlProjectDescription(ProjectNumber As String) As Boolean
        Dim bRet As Boolean = False
        Try
            Dim dFile As String = ClientConfig.Configuration.ClientLocation & "\FAHControl.db"
            If Not My.Computer.FileSystem.FileExists(dFile) Then Exit Try
            Dim Description As String = String.Empty
            Using dbInstFC As clsDB.clsDBInstance = dbPool.dbInstance(-1, "Data source=" & dFile & ";mode=ro")
                With dbInstFC
                    .TryOpenCon()
                    If .conState = ConnectionState.Open Then
                        Dim bHas As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sql_master WHERE name=projects")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            WriteDebug("FAHControl has no record of this project")
                            Exit Try
                        End If
                        Using rdr As SQLiteDataReader = .dataReader("SELECT description FROM projects WHERE id='" & ProjectNumber & "'")
                            If rdr.HasRows Then
                                Description = CStr(rdr.Item(0))
                            End If
                        End Using
                    Else
                        WriteLog("Sqlite connection failed for FAHControl.db", eSeverity.Debug)
                    End If
                End With
            End Using

            If Not Description = String.Empty Then
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Try
                                sqlErr = .ExecuteNonQuery("UPDATE ProjectInfo SET Description='" & Description & "' WHERE ProjectNumber='" & ProjectNumber & "'")
                            Catch ex As Exception
                                WriteError(ex.Message, Err)
                            End Try
                            bRet = True
                        Else
                            WriteLog("Can't access the database, there should be more errors", eSeverity.Debug)
                        End If
                    End With
                End Using
            End If
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return bRet
    End Function
#End Region
#End Region
#Region "SQL queries"
    Public ReadOnly Property InitSQLFilters As Boolean
        Get
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Dim bhas As Boolean
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='Filters'")
                                bhas = rdr.HasRows
                            End Using
                            If Not bhas Then
                                sqlErr = .ExecuteNonQuery("CREATE TABLE Filters (name TEXT, sql TEXT)")
                                sqlErr = .ExecuteNonQuery("INSERT INTO Filters (name, sql) VALUES('EUE','" & FormatSQLString(" WHERE NOT CoreStatus LIKE '%FINISHED_UNIT%' AND NOT CoreStatus LIKE '%dumped%' ORDER BY Downloaded DESC") & "')")
                                sqlErr = .ExecuteNonQuery("INSERT INTO Filters (name, sql) VALUES('dumped','" & FormatSQLString(" WHERE CoreStatus LIKE '%dumped%' ORDER BY Downloaded DESC") & "')")
                                sqlErr = .ExecuteNonQuery("INSERT INTO Filters (name, sql) VALUES('Not submitted','" & FormatSQLString(" WHERE CoreStatus LIKE '%FINISHED_UNIT%' AND ServerResponse='' ORDER BY Completed DESC") & "')")
                                sqlErr = .ExecuteNonQuery("INSERT INTO Filters (name, sql) VALUES('Submitted','" & FormatSQLString(" WHERE NOT ServerResponse = '' ORDER BY Completed ASC") & "')")
                                GoTo ReadFilters
                            Else
ReadFilters:
                                Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM Filters")
                                    While rdr.Read
                                        sqlFilters.AddFilter(CStr(rdr.Item("name")), FormatSQLString(CStr(rdr.Item("sql")), True))
                                    End While
                                End Using
                            End If
                            If sqlFilters.FilterNames.Contains("EUE") And sqlFilters.FilterNames.Contains("dumped") Then
                                Return True
                            Else
                                Return False
                            End If
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                            Return False
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
                Return False
            End Try
        End Get
    End Property
    Public Function StoreFilters() As Boolean
        Dim bRet As Boolean = False
        Try
            'Check
            Dim dbInst As clsDB.clsDBInstance = dbPool.dbInstance
            With dbInst
                If .conState = ConnectionState.Open Then
                    Dim bhas As Boolean
                    Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='Filters'")
                        bhas = rdr.HasRows
                    End Using
                    If Not bhas Then
                        sqlErr = .ExecuteNonQuery("CREATE TABLE Filters (name TEXT, sql TEXT)")
                    End If
                    For Each sqlFilter In sqlFilters.Filters
                        Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM Filters WHERE name='" & sqlFilter.Key & "'")
                            bhas = rdr.HasRows
                        End Using
                        If bhas Then
                            sqlErr = .ExecuteNonQuery("UPDATE Filters SET sql='" & FormatSQLString(sqlFilter.Value) & "' WHERE name='" & sqlFilter.Key & "'")
                        Else
                            sqlErr = .ExecuteNonQuery("INSERT INTO Filters (name, sql) VALUES('" & sqlFilter.Key & "','" & FormatSQLString(sqlFilter.Value) & "')")
                        End If
                    Next
                    bRet = True
                Else
                    WriteDebug("Can't access the database, there should be more errors")
                End If
            End With
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return bRet
    End Function
#Region "Statistics"
    Public ReadOnly Property Statistics(Optional startDate As DateTime = #1/1/2000#, Optional Limit As Boolean = False) As clsStatistics
        Get
            Dim rVal As New clsStatistics

            'Get totals
            'SELECT client, slot, hw, TOTAL(credit) as 'total_credit', TOTAL(1) AS 'total_workunits', AVG(ppd) as 'Avg_ppd' FROM 'WorkUnits' GROUP BY client, slot, hw 
            Try

                For Each sClient As clsClients.clsClient In logParser.Clients
                    Dim nClient As New clsStatistics.sClient
                    nClient.Name = sClient.ClientName
                    rVal.Clients.Add(nClient.Name, nClient)
                    Dim iWuCompleted As Int32 = 0
                    Dim iWuEUE As Int32 = 0
                    Dim iTotalCredit As Int32 = 0
                    Dim dblAveragePPD As Double = 0.0
                    Dim tsComputation As New TimeSpan(0)
                    'Do slots, combine for client
                    For xInt As Int32 = 0 To sClient.ClientConfig.Configuration.slots.Count - 1
                        Dim cSlot As clsClientConfig.clsConfiguration.sSlot = sClient.ClientConfig.Configuration.slots(xInt)
                        Dim sSlot As New clsStatistics.sClient.sSlot
                        If cSlot.id.Length = 1 Then
                            sSlot.ID = "0" & cSlot.id
                        Else
                            sSlot.ID = cSlot.id
                        End If
                        If cSlot.type = "GPU" Then
                            If sClient.ClientConfig.Configuration.Slot(cSlot.id).HasKey("gpu-index") Then
                                Dim sID As Int32 = CInt(sClient.ClientConfig.Configuration.Slot(cSlot.id).GetValue("gpu-index"))
                                sSlot.Hardware = sClient.ClientInfo.Info.GPU(sID).DeviceName
                            Else
                                'gpu index = 0
                                sSlot.Hardware = sClient.ClientInfo.Info.GPU(0).DeviceName
                            End If
                        Else
                            sSlot.Hardware = cSlot.type
                        End If

                        rVal.Clients(sClient.ClientName).Slots.Add(sSlot.ID, sSlot)
                        Try
                            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                                With dbInst
                                    If .conState = ConnectionState.Open Then
                                        Dim sqlQuery As String = ""
                                        If startDate = #1/1/2000# Then
                                            sqlQuery = "SELECT Slot, TOTAL(credit) as 'total_credit', COUNT(1) AS 'workunits', AVG(ppd) as 'avg_ppd', HW FROM 'WorkUnits' WHERE Client='" & FormatSQLString(sClient.ClientName, False, True) & "' AND Slot='" & sSlot.ID & "' AND HW LIKE '" & sSlot.Hardware & "%' GROUP BY Slot"
                                        Else
                                            sqlQuery = "SELECT Slot, TOTAL(credit) as 'total_credit', COUNT(1) AS 'workunits', AVG(ppd) as 'avg_ppd', HW FROM 'WorkUnits' WHERE Client='" & FormatSQLString(sClient.ClientName, False, True) & "' AND Slot='" & sSlot.ID & "' AND HW LIKE '" & sSlot.Hardware & "%' AND Completed > '" & startDate.ToString("s") & "' GROUP BY Slot"
                                        End If
                                        Using rdr As SQLiteDataReader = .dataReader(sqlQuery)
                                            While rdr.Read
                                                rVal.Clients(sClient.ClientName).Slots(sSlot.ID).WU_Completed = CStr(rdr.Item("workunits"))
                                                rVal.Clients(sClient.ClientName).Slots(sSlot.ID).TotalCredit = CStr(rdr.Item("total_credit"))
                                                rVal.Clients(sClient.ClientName).Slots(sSlot.ID).AveragePPD = CStr(Math.Round(CDbl(rdr.Item("avg_ppd")), 2))
                                            End While
                                        End Using
                                        If rVal.Clients(sClient.ClientName).Slots(sSlot.ID).WU_Completed <> "" Then iWuCompleted += CInt(rVal.Clients(sClient.ClientName).Slots(sSlot.ID).WU_Completed)
                                        If rVal.Clients(sClient.ClientName).Slots(sSlot.ID).TotalCredit <> "" Then iTotalCredit += CInt(rVal.Clients(sClient.ClientName).Slots(sSlot.ID).TotalCredit)
                                        If rVal.Clients(sClient.ClientName).Slots(sSlot.ID).AveragePPD <> "" Then
                                            If dblAveragePPD = 0.0 Then
                                                dblAveragePPD = CDbl(rVal.Clients(sClient.ClientName).Slots(sSlot.ID).AveragePPD)
                                            Else
                                                dblAveragePPD = (dblAveragePPD + CDbl(rVal.Clients(sClient.ClientName).Slots(sSlot.ID).AveragePPD)) / 2
                                            End If
                                        End If
                                    Else
                                        WriteLog("Can't access the database, there should be more errors in the log", eSeverity.Critical)
                                    End If
                                End With
                            End Using
                        Catch ex As Exception
                            WriteError(ex.Message, Err)
                        End Try
                        'Get eue
                        Try
                            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                                With dbInst
                                    If .conState = ConnectionState.Open Then
                                        Dim sqlQuery As String = ""
                                        If startDate = #1/1/2000# Then
                                            sqlQuery = "SELECT Slot, COUNT(1) AS 'eue' FROM 'WorkUnits' WHERE NOT CoreStatus LIKE '%FINISHED_UNIT%' AND Client='" & FormatSQLString(sClient.ClientName, False, True) & "' AND Slot='" & sSlot.ID & "' AND HW LIKE '" & sSlot.Hardware & "%' GROUP BY Slot"
                                        Else
                                            sqlQuery = "SELECT Slot, COUNT(1) AS 'eue' FROM 'WorkUnits' WHERE NOT CoreStatus LIKE '%FINISHED_UNIT%' AND Client='" & FormatSQLString(sClient.ClientName, False, True) & "' AND Slot='" & sSlot.ID & "' AND HW LIKE '" & sSlot.Hardware & "%' AND Completed > '" & startDate.ToString("s") & "' GROUP BY Slot"
                                        End If
                                        Using rdr As SQLiteDataReader = .dataReader(sqlQuery)
                                            While rdr.Read
                                                rVal.Clients(sClient.ClientName).Slots(sSlot.ID).WU_EUE = CStr(rdr.Item("eue"))
                                            End While
                                        End Using
                                        If rVal.Clients(sClient.ClientName).Slots(sSlot.ID).WU_EUE <> "" Then
                                            iWuEUE += CInt(rVal.Clients(sClient.ClientName).Slots(sSlot.ID).WU_EUE)
                                        End If
                                    Else
                                        WriteLog("Can't access the database, there should be more errors in the log", eSeverity.Critical)
                                    End If
                                End With
                            End Using
                        Catch ex As Exception
                            WriteError(ex.Message, Err)
                        End Try
                        'Get computation time?
                        Try
                            If Limit Then Exit Try 'Don't include computation time 
                            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                                With dbInst
                                    If .conState = ConnectionState.Open Then
                                        Dim tsTotal As New TimeSpan
                                        Dim sqlQuery As String = ""
                                        If startDate = #1/1/2000# Then
                                            sqlQuery = "SELECT Started, Completed FROM 'WorkUnits' WHERE client='" & FormatSQLString(sClient.ClientName, False, True) & "' AND Slot='" & sSlot.ID & "' AND HW LIKE '" & sSlot.Hardware & "%'"
                                        Else
                                            sqlQuery = "SELECT Started, Completed FROM 'WorkUnits' WHERE client='" & FormatSQLString(sClient.ClientName, False, True) & "' AND Slot='" & sSlot.ID & "' AND HW LIKE '" & sSlot.Hardware & "%' AND Completed > '" & startDate.ToString("s") & "'"
                                        End If
                                        Using rdr As SQLiteDataReader = .dataReader(sqlQuery)
                                            While rdr.Read
                                                Dim tsAdd As New TimeSpan
                                                tsAdd = CDate(rdr.Item("Completed")).Subtract(CDate(rdr.Item("Started")))
                                                tsTotal += tsAdd
                                            End While
                                            rVal.Clients(sClient.ClientName).Slots(sSlot.ID).ComputationTime = tsTotal.ToString("c")
                                        End Using
                                        If tsTotal > TimeSpan.FromTicks(0) Then
                                            tsComputation = tsComputation.Add(tsTotal)
                                        End If
                                    Else
                                        WriteLog("Can't access the database, there should be more errors in the log", eSeverity.Critical)
                                    End If
                                End With
                            End Using
                        Catch ex As Exception
                            WriteError(ex.Message, Err)
                        End Try
                    Next
                    'Combine slots
                    rVal.Clients(sClient.ClientName).AveragePPD = CStr(Math.Round(dblAveragePPD, 2))
                    rVal.Clients(sClient.ClientName).ComputationTime = tsComputation.ToString("c")
                    rVal.Clients(sClient.ClientName).TotalCredit = CStr(iTotalCredit)
                    rVal.Clients(sClient.ClientName).WU_Completed = CStr(iWuCompleted)
                    rVal.Clients(sClient.ClientName).WU_EUE = CStr(iWuEUE)
                Next


                For Each C As clsStatistics.sClient In rVal.Clients.Values.ToList
                    If Not C.WU_Completed = "" Then
                        If rVal.WU_Completed <> "" Then
                            rVal.WU_Completed = CStr(CInt(rVal.WU_Completed) + CInt(C.WU_Completed))
                        Else
                            rVal.WU_Completed = C.WU_Completed
                        End If
                    End If
                    If Not C.WU_EUE = "" Then
                        If rVal.WU_EUE <> "" Then
                            rVal.WU_EUE = CStr(CInt(rVal.WU_EUE) + CInt(C.WU_EUE))
                        Else
                            rVal.WU_EUE = C.WU_EUE
                        End If
                    End If

                    If Not C.ComputationTime = "" Then
                        If Not rVal.ComputationTime = "" Then
                            rVal.ComputationTime = TimeSpan.Parse(rVal.ComputationTime).Add(TimeSpan.Parse(C.ComputationTime)).ToString
                        Else
                            rVal.ComputationTime = C.ComputationTime
                        End If
                    End If

                    If Not C.TotalCredit = "" Then
                        If Not rVal.TotalCredit = "" Then
                            rVal.TotalCredit = CStr(CInt(rVal.TotalCredit) + CInt(C.TotalCredit))
                        Else
                            rVal.TotalCredit = C.TotalCredit
                        End If
                    End If

                    If Not C.AveragePPD = "" Then
                        If Not rVal.TotalCredit = "" Then
                            rVal.AveragePPD = Math.Round((CDbl(rVal.AveragePPD) + CDbl(C.AveragePPD)) / 2, 2).ToString
                        Else
                            rVal.TotalCredit = C.TotalCredit
                        End If
                    End If
                Next
                rVal.dtStatistics = DateTime.Now
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            'SELECT client, slot, hw, TOTAL(credit) as 'total_credit', TOTAL(1) AS 'total_workunits', AVG(ppd) as 'Avg_ppd' FROM 'WorkUnits' GROUP BY client, slot, hw 

            'SELECT client, slot, hw, TOTAL(credit) as 'credit', COUNT(1) AS 'workunits' FROM 'WorkUnits', COUNT(1) AS 'eue' FROM 'WorkUnits' WHERE NOT 'CoreStatus' LIKE '%FINISHED_UNIT%' GROUP BY client, slot, hw
            Return rVal
        End Get
    End Property
#End Region
#End Region
#Region "Diagnostics"
    Public ReadOnly Property IsDiagnosticsStored(ClientName As String, dtDiagnostics As String) As Boolean
        Get
            Dim bRet As Boolean = False
            Try
                'Not used/finished
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Dim rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='Diagnostics'")
                            Dim bHas As Boolean = rdr.HasRows
                            rdr.Close()
                            If Not bHas Then Exit Try

                            bRet = True
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return bRet
        End Get
    End Property

    Public Sub StoreDiagnostics(ClientName As String, dtDiagnostics As String, Diagnostics As String)
        Try
            Throw New NotImplementedException
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
    End Sub
#End Region
#Region "Should this log be checked?"
    Public ReadOnly Property IsLogStored(ClientName As String, FileName As String) As Boolean
        Get
            Dim rVal As Boolean = False
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Dim bHas As Boolean
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='FinishedLogs'")
                                bHas = rdr.HasRows
                            End Using
                            If Not bHas Then Exit Try
                            Dim sQuery As String = "SELECT FileName FROM 'FinishedLogs' WHERE ClientName='" & FormatSQLString(ClientName, False, True) & "' AND FileName='" & FormatSQLString(FileName.Replace(".txt", ""), False, True) & "'"
                            Using rdr As SQLiteDataReader = .dataReader(sQuery)
                                rVal = rdr.HasRows
                            End Using
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
                rVal = False
            End Try
            Return rVal
        End Get
    End Property
    Public Sub SetLogStored(ClientName As String, FileName As String, LogString As String, LineCount As Int32)
        Try
            If IsLogStored(ClientName, FileName) Then Exit Sub
            WriteDebug("Setting log stored for " & ClientName & " - " & FileName)
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        'Dim bDoCommit As Boolean = Not .TransactionInProgress
                        'If Not .TransactionInProgress Then
                        '    .BeginTransaction()
                        'End If
                        Dim bHas As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='FinishedLogs'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            'CreateTable
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'FinishedLogs' (ClientName TEXT, FileName TEXT, LogString TEXT, LineCount TEXT)")
                        End If
                        sqlErr = .ExecuteNonQuery("INSERT INTO 'FinishedLogs' (ClientName, FileName, LogString, LineCount) VALUES('" & FormatSQLString(ClientName, False, True) & sib & FormatSQLString(FileName, False, True) & sib & FormatSQLString(LogString) & sib & LineCount.ToString & "')")
                        'If bDoCommit Then .EndTransaction()
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
    End Sub
    Public ReadOnly Property StoredLog(ClientName As String, FileName As String) As clsClients.clsClient.clsLogFile
        Get
            Dim lSection As New clsClients.clsClient.clsLogFile(ClientName)
            Try
                If Not IsLogStored(ClientName, FileName) Then Return lSection
                lSection.File = FileName
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Dim rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'Finishedlogs' WHERE ClientName='" & FormatSQLString(ClientName, False, True) & "' AND FileName='" & FormatSQLString(FileName, False, True) & "'")
                            While rdr.Read
                                lSection.Log = FormatSQLString(CStr(rdr.Item("LogString")), True)
                                lSection.LineCount = CStr(rdr.Item("LineCount"))
                            End While
                            rdr.Close()
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return lSection
        End Get
    End Property
#End Region
#Region "FAHCLient INFO"
    Public ReadOnly Property HasClientInfo(ClientName As String, LogDate As DateTime) As Boolean
        Get
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst

                        If .conState = ConnectionState.Open Then
                            Dim bHas As Boolean
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='ClientInfo'")
                                bHas = rdr.HasRows
                            End Using
                            If Not bHas Then Return False
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ClientInfo' WHERE Client='" & ClientName & "' AND LogDate='" & LogDate.ToString("s") & "'")
                                bHas = rdr.HasRows
                            End Using
                            Return bHas
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                            Return False
                        End If
                    End With
                End Using

            Catch ex As Exception
                WriteError(ex.Message, Err)
                Return False
            End Try
        End Get
    End Property
    Public Sub SaveFAHClientInfo(ClientName As String, LogDate As DateTime, ClientInf As clsClientInfo.FAHClientInfo)
        Try
            If HasClientInfo(ClientName, LogDate) Then Exit Sub
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        Dim bDoCommit As Boolean = Not .TransactionInProgress
                        If Not .TransactionInProgress Then
                            .BeginTransaction()
                        End If
                        If DebugOutput Then WriteDebug("Storing client info for " & ClientName & " - " & LogDate.ToString & " - " & ClientInf.Report)
                        Dim bHas As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='ClientInfo'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'ClientInfo' (Client TEXT, LogDate DATETIME, Info TEXT)")
                        End If
                        Dim sbValues As New StringBuilder
                        For xInt As Int32 = 0 To ClientInf.Keys.Count - 1
                            sbValues.Append(ClientInf.Keys(xInt) & "==" & ClientInf(ClientInf.Keys(xInt)) & ",,,")
                        Next
                        sbValues.Remove(sbValues.Length - 3, 3)
                        sqlErr = .ExecuteNonQuery("INSERT INTO 'ClientInfo' (Client, LogDate, Info) VALUES('" & ClientName & "','" & LogDate.ToString("s") & "','" & FormatSQLString(sbValues.ToString) & "')")
                        If bDoCommit Then .EndTransaction()
                    Else
                        If DebugOutput Then WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
    End Sub
    Public ReadOnly Property ClientInfoBeforeDT(ClientName As String, LogDate As DateTime) As clsClientInfo.FAHClientInfo
        'Use this to find ClientInfo for workunits
        'split(sbvalues.ToString ,",,,",,CompareMethod.Text )
        Get
            Dim rCI As New clsClientInfo.FAHClientInfo
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ClientInfo' WHERE Client='" & ClientName & "' ORDER BY LogDate DESC")
                                While rdr.Read
                                    rCI.Clear()
                                    Dim iInf As String = CStr(rdr.Item("Info"))
                                    Dim iArr As Array = Split(iInf, ",,,", , CompareMethod.Text)
                                    For Each iStr As String In iArr
                                        rCI.Add(iStr.Substring(0, iStr.IndexOf("==")), iStr.Substring(iStr.IndexOf("==") + 2))
                                    Next
                                    If CDate(rdr.Item("LogDate")) > LogDate Then
                                        Exit While
                                    End If
                                End While
                            End Using
                        Else
                            If DebugOutput Then WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return rCI
        End Get
    End Property
#End Region
#Region "FAHClient CONFIG"
    Public Overloads ReadOnly Property HasClientConfig(ClientName As String, Config As String, dtConfig As DateTime) As Boolean
        Get
            Dim rVal As Boolean = False
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='ClientConfig'")
                                rVal = rdr.HasRows
                            End Using
                            If Not rVal Then Exit Try
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ClientConfig' WHERE Client='" & FormatSQLString(ClientName, False, True) & "' AND ConfigString='" & FormatSQLString(Config) & "' AND LogDate='" & dtConfig.ToString("s") & "'")
                                rVal = rdr.HasRows
                            End Using
                        Else
                            WriteLog("Can't access the database, there should be more errors", eSeverity.Important)
                            Return False
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
                rVal = False
            End Try
            Return rVal
        End Get
    End Property
    'Public Overloads ReadOnly Property HasClientConfig(ClientName As String, LogDate As DateTime) As Boolean
    '    Get
    '        Try
    '            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
    '                With dbInst
    '                    If .conState = ConnectionState.Open Then
    '                        Dim bHas As Boolean
    '                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='ClientConfig'")
    '                            bHas = rdr.HasRows
    '                        End Using
    '                        If Not bHas Then Return False
    '                        Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ClientConfig' WHERE Client='" & ClientName & "' AND LogDate='" & LogDate.ToString("s") & "'")
    '                            bHas = rdr.HasRows
    '                        End Using
    '                        Return bHas
    '                    Else
    '                        WriteLog("Can't access the database, there should be more errors", eSeverity.Important)
    '                        Return False
    '                    End If
    '                End With
    '            End Using
    '        Catch ex As Exception
    '            WriteError(ex.Message, Err)
    '            Return False
    '        End Try
    '    End Get
    'End Property
    Public Sub SaveFAHClientConfig(ClientName As String, LogDate As DateTime, ClientCFG As clsClientConfig.clsConfiguration)
        Try
            If HasClientConfig(ClientName, ClientCFG.ConfigString, LogDate) Then
                Exit Sub
            End If
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Dim bDoCommit As Boolean = Not .TransactionInProgress
                        If Not .TransactionInProgress Then
                            .BeginTransaction()
                        End If
                        Dim bHas As Boolean
                        If DebugOutput Then WriteDebug("Saving fahclient config for " & ClientName & " - " & LogDate.ToString & " - " & ClientCFG.Report(True))
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='ClientConfig'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'ClientConfig' (Client TEXT, LogDate DATETIME, ConfigString TEXT)")
                        End If
                        sqlErr = .ExecuteNonQuery("INSERT INTO 'ClientConfig' (Client, LogDate, ConfigString) VALUES ('" & FormatSQLString(ClientName, False, True) & sib & LogDate.ToString("s") & sib & FormatSQLString(ClientCFG.ConfigString) & "')")
                        If bDoCommit Then .EndTransaction()
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
    End Sub
    Public ReadOnly Property ClientConfigBeforeDT(ClientName As String, LogDate As DateTime) As clsClientConfig.clsConfiguration
        'Use this to find clientconfig per work unit 
        Get
            Dim rCC As New clsClientConfig.clsConfiguration
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'ClientConfig' WHERE Client='" & FormatSQLString(ClientName, False, True) & "'")
                                While rdr.Read
                                    If CDate(rdr.Item("LogDate")) > LogDate Then
                                        Exit While
                                    End If
                                    rCC.ReadString(FormatSQLString(CStr(rdr.Item("ConfigString")), True, True))
                                End While
                            End Using
                            Return rCC
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return rCC
        End Get
    End Property
#End Region
#Region "Work unit"
    Public ReadOnly Property HasWorkUnit(ClientName As String, SlotID As String, unit As String, Downloaded As DateTime) As Boolean
        Get
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst

                        If .conState = ConnectionState.Open Then
                            Dim bHas As Boolean
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='WorkUnits'")
                                bHas = rdr.HasRows
                            End Using
                            If Not bHas Then Return False
                            If SlotID.Length = 1 Then SlotID = "0" & SlotID
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'WorkUnits' WHERE unit='" & unit & "' AND Slot='" & SlotID & "' AND Client='" & FormatSQLString(ClientName, False, True) & "' AND Downloaded='" & Downloaded.ToString("s") & "'")
                                bHas = rdr.HasRows
                            End Using
                            Return bHas
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                            Return False
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
                Return False
            End Try
        End Get
    End Property
    Public Overloads Sub SaveWorkUnit(TheList As List(Of clsWU), Optional ShowBussy As Boolean = False)
        Dim sWU As New List(Of clsWU)
        For Each WU In TheList
            If Not HasWorkUnit(WU.ClientName, WU.Slot, WU.unit, WU.dtDownloaded) Then
                sWU.Add(WU)
            End If
        Next
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        Dim bTransaction As Boolean = Not .TransactionInProgress
                        If Not .TransactionInProgress Then
                            .BeginTransaction()
                        End If
                        Dim bHas As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='WorkUnits'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'WorkUnits' (Client TEXT, Slot TEXT, HW TEXT, Project TEXT, RCG TEXT, unit TEXT, CS TEXT, WS TEXT, CoreStatus TEXT, ServerResponse TEXT, CoreSnippet TEXT, CoreVersion TEXT, CoreCompiler TEXT, Core TEXT, BoardType TEXT, Downloaded DATETIME, Started DATETIME, Completed DATETIME, Submitted DATETIME, Credit REAL, PPD REAL,UploadSize TEXT, dblUploadSize REAL, StartUpload DATETIME, TPF TEXT)")
                        End If
                        Dim iWu As Int32 = 0
                        For Each wu As clsWU In sWU
                            iWu += 1
                            'Save wu
                            If ShowBussy Then
                                Bussy.SetMessage("Storing parse results for " & wu.ClientName & Environment.NewLine & "Downloaded: " & wu.dtDownloaded.ToString & Environment.NewLine & wu.PRCG)
                            End If
                            Try
                                Dim qStr As String = "INSERT INTO 'WorkUnits' (Client, Slot, HW, Project, RCG, unit, CS, WS, CoreStatus, ServerResponse, CoreSnippet, CoreVersion, CoreCompiler, Core, BoardType, Downloaded, Started, Completed, Submitted, Credit, PPD, UploadSize, StartUpload, TPF, dblUploadSize) VALUES('" & FormatSQLString(wu.ClientName, False, True) & sib & wu.Slot & sib & wu.HW & sib & wu.Project & sib & wu.RCG & sib & wu.unit & sib & wu.CS & sib & wu.WS & sib & wu.CoreStatus & sib & wu.ServerResponce & sib & FormatSQLString(wu.CoreSnippet) & sib & wu.CoreVersion & sib & wu.CoreCompiler & sib & wu.Core & sib & wu.BoardType & sib & wu.dtDownloaded.ToString("s") & sib & wu.dtStarted.ToString("s") & sib & wu.dtCompleted.ToString("s") & sib & wu.dtSubmitted.ToString("s") & sib & wu.Credit & sib & wu.PPD & sib & wu.UploadSize & sib & wu.dtStartUpload.ToString("s") & sib & wu.tsTPF.ToString("c") & sib & wu.iUploadSize & "')"
                                sqlErr = .ExecuteNonQuery(qStr)
                            Catch ex As Exception
                                WriteError(ex.Message, Err)
                            End Try

                            'Save frames

                            If wu.Frames.Count > 0 Then
                                Try
                                    Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='FrameInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "'")
                                        bHas = rdr.HasRows
                                    End Using
                                    If bHas Then
                                        If DebugOutput Then WriteDebug("Existing frame data for " & wu.PRCG & "-" & wu.unit & " will be removed")
                                        sqlErr = .ExecuteNonQuery("DELETE FROM 'FrameInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "'")
                                    Else
                                        sqlErr = .ExecuteNonQuery("CREATE TABLE 'FrameInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "' (dtPercentage DATETIME, Percentage INTEGER)")
                                    End If
                                    'transactions moved to whole parsing loop

                                    For Each fInfo In wu.Frames
                                        'Bussy.SetMessage("Storing parse results for " & wu.ClientName & Environment.NewLine & "Downloaded: " & wu.dtDownloaded.ToString & Environment.NewLine & wu.PRCG & Environment.NewLine & "Frame " & fInfo.strPercentage & " - " & fInfo.FrameDT.ToString)
                                        sqlErr = .ExecuteNonQuery("INSERT INTO 'FrameInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "' (dtPercentage, Percentage) VALUES('" & fInfo.FrameDT.ToString("s") & "','" & fInfo.strPercentage & "')")
                                    Next
                                Catch

                                End Try
                            End If

                            If wu.bHasRestarted AndAlso wu.restartInfo.RestartInfo.Count > 0 Then
                                Try
                                    Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='RestartInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "'")
                                        bHas = rdr.HasRows
                                    End Using

                                    If Not bHas Then
                                        If DebugOutput Then WriteDebug("Creating table for restart info for work unit: " & wu.unit)
                                        sqlErr = .ExecuteNonQuery("CREATE TABLE 'RestartInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "' (dtFrame DATETIME, Frame TEXT, CoreStatus TEXT)")
                                    Else
                                        If DebugOutput Then WriteDebug("Cleaning existing restart information table for unit: " & wu.unit)
                                        sqlErr = .ExecuteNonQuery("DELETE FROM 'RestartInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "'")
                                    End If

                                    'transactions moved to whole parsing loop
                                    For Each rInfo In wu.restartInfo.RestartInfo
                                        'Bussy.SetMessage("Storing parse results for " & wu.ClientName & Environment.NewLine & "Downloaded: " & wu.dtDownloaded.ToString & " - " & wu.PRCG & "Restart info " & rInfo.CoreStatus & " frame " & rInfo.LastFrame.strPercentage)
                                        sqlErr = .ExecuteNonQuery("INSERT INTO 'RestartInfo_" & FormatSQLString(wu.ClientName, False, True) & "_" & wu.Slot & "_" & wu.unit & "' (dtFrame, Frame, CoreStatus) VALUES('" & rInfo.LastFrame.FrameDT.ToString("s") & sib & rInfo.LastFrame.strPercentage & sib & rInfo.CoreStatus & "')")
                                    Next
                                Catch ex As Exception
                                    WriteError(ex.Message, Err)
                                End Try
                            End If
SkipWU:
                        Next
                        If bTransaction Then .EndTransaction()
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
    End Sub

    '    Public Overloads Sub SaveWorkUnit(ClientName As String, Wu As clsWU)
    '        Try
    '            If HasWorkUnit(ClientName, Wu.Slot, Wu.unit, Wu.dtDownloaded) Then Exit Sub
    '            WriteDebug("Storing work unit " & Wu.PRCG & " - " & Wu.unit)
    '            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
    '                With dbInst

    '                    If .conState = ConnectionState.Open Then
    '                        Dim bTransaction As Boolean = Not .TransactionInProgress
    '                        If Not .TransactionInProgress Then
    '                            .BeginTransaction()
    '                        End If
    '                        Dim bHas As Boolean
    '                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='WorkUnits'")
    '                            bHas = rdr.HasRows
    '                        End Using
    '                        If Not bHas Then
    '                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'WorkUnits' (Client TEXT, Slot TEXT, HW TEXT, Project TEXT, RCG TEXT, unit TEXT, CS TEXT, WS TEXT, CoreStatus TEXT, ServerResponse TEXT, CoreSnippet TEXT, CoreVersion TEXT, CoreCompiler TEXT, Core TEXT, BoardType TEXT, Downloaded DATETIME, Started DATETIME, Completed DATETIME, Submitted DATETIME, Credit REAL, PPD REAL,UploadSize TEXT, StartUpload DATETIME, TPF TEXT)")
    '                        End If
    '                        Try
    '                            sqlErr = .ExecuteNonQuery("INSERT INTO 'WorkUnits' (Client, Slot, HW, Project, RCG, unit, CS, WS, CoreStatus, ServerResponse, CoreSnippet, CoreVersion, CoreCompiler, Core, BoardType, Downloaded, Started, Completed, Submitted, Credit, PPD, UploadSize, StartUpload) VALUES('" & ClientName & sib & Wu.Slot & sib & Wu.HW & sib & Wu.Project & sib & Wu.RCG & sib & Wu.unit & sib & Wu.CS & sib & Wu.WS & sib & Wu.CoreStatus & sib & Wu.ServerResponce & sib & FormatSQLString(Wu.CoreSnippet) & sib & Wu.CoreVersion & sib & Wu.CoreCompiler & sib & Wu.Core & sib & Wu.BoardType & sib & Wu.dtDownloaded.ToString("s") & sib & Wu.dtStarted.ToString("s") & sib & Wu.dtCompleted.ToString("s") & sib & Wu.dtSubmitted.ToString("s") & sib & Wu.Credit & sib & Wu.PPD & sib & Wu.UploadSize & sib & Wu.dtStartUpload.ToString("s") & sib & wu.tpf.tostring("c") & "')")
    '                        Catch ex As Exception
    '                            WriteError(ex.Message, Err)
    '                        End Try
    '                        If bTransaction Then .EndTransaction()
    '                    Else
    '                        WriteDebug("Can not access the database, there should be more errors")
    '                    End If
    '                End With
    '            End Using
    '            If Wu.Frames.Count > 0 Then
    '                Try
    '                    Using dbInstCheck As clsDB.clsDBInstance = dbPool.dbInstance
    '                        With dbInstCheck

    '                            If .conState = ConnectionState.Open Then
    '                                Dim bHas As Boolean

    '                                Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='FrameInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "'")
    '                                    bHas = rdr.HasRows
    '                                End Using
    '                                If bHas Then
    '                                    WriteLog("Existing frame data for " & Wu.PRCG & "-" & Wu.unit & " will be removed", eSeverity.Important)
    '                                    sqlErr = .ExecuteNonQuery("DELETE FROM 'FrameInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "'")
    '                                Else
    '                                    sqlErr = .ExecuteNonQuery("CREATE TABLE 'FrameInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "' (dtPercentage DATETIME, Percentage INTEGER)")
    '                                End If
    '                            Else
    '                                WriteDebug("Can not access the database, there should be more errors")
    '                            End If
    '                        End With
    '                    End Using
    '                    'transactions moved to whole parsing loop
    '                    Using dbInstFrames As clsDB.clsDBInstance = dbPool.dbInstance
    '                        With dbInstFrames

    '                            If .conState = ConnectionState.Open Then
    '                                Dim bDoCommit As Boolean = Not .TransactionInProgress
    '                                If Not .TransactionInProgress Then
    '                                    .BeginTransaction()
    '                                End If
    '                                For Each fInfo In Wu.Frames
    '                                    sqlErr = .ExecuteNonQuery("INSERT INTO 'FrameInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "' (dtPercentage, Percentage) VALUES('" & fInfo.FrameDT.ToString("s") & "','" & fInfo.strPercentage & "')")
    '                                Next
    '                                If bDoCommit Then .EndTransaction()
    '                            Else
    '                                WriteDebug("Can not access the database, there should be more errors")
    '                            End If
    '                        End With
    '                    End Using
    '                Catch ex As Exception
    '                    WriteError(ex.Message, Err)
    '                End Try
    '            End If
    'SkipFrames:

    '            'If Restarted then table 'RestartInfo_" & clientname & "_" & unit & "'" should be made
    '            If Wu.bHasRestarted AndAlso Wu.restartInfo.RestartInfo.Count > 0 Then
    '                Try
    '                    Using dbInstCheck As clsDB.clsDBInstance = dbPool.dbInstance
    '                        Dim bTable As Boolean = False
    '                        With dbInstCheck
    '                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='RestartInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "'")
    '                                bTable = rdr.HasRows
    '                            End Using
    '                            If Not bTable Then
    '                                WriteDebug("Creating table for restart info for work unit: " & Wu.unit)
    '                                sqlErr = .ExecuteNonQuery("CREATE TABLE 'RestartInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "' (dtFrame DATETIME, Frame TEXT, CoreStatus TEXT)")
    '                            Else
    '                                WriteLog("Cleaning existing restart information table for unit: " & Wu.unit)
    '                                sqlErr = .ExecuteNonQuery("DELETE FROM 'RestartInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "'")
    '                            End If
    '                        End With
    '                    End Using

    '                    'transactions moved to whole parsing loop
    '                    Using dbInstRestart As clsDB.clsDBInstance = dbPool.dbInstance
    '                        With dbInstRestart

    '                            If .conState = ConnectionState.Open Then
    '                                Dim bDoCommit As Boolean = Not .TransactionInProgress
    '                                If Not .TransactionInProgress Then
    '                                    .BeginTransaction()
    '                                End If
    '                                For Each rInfo In Wu.restartInfo.RestartInfo
    '                                    sqlErr = .ExecuteNonQuery("INSERT INTO 'RestartInfo_" & ClientName & "_" & Wu.Slot & "_" & Wu.unit & "' (dtFrame, Frame, CoreStatus) VALUES('" & rInfo.LastFrame.FrameDT.ToString("s") & sib & rInfo.LastFrame.strPercentage & sib & rInfo.CoreStatus & "')")
    '                                Next
    '                                .EndTransaction()
    '                            Else
    '                                WriteDebug("Can't access the database, there should be more errors")
    '                            End If
    '                        End With
    '                    End Using
    '                Catch ex As Exception
    '                    WriteError(ex.Message, Err)
    '                End Try
    '            End If
    '        Catch ex As Exception
    '            WriteError(ex.Message, Err)
    '        End Try
    '    End Sub
    Public Function StoreActiveWU(WU As clsWU) As Boolean
        Try
            'If you have a wu which has been waiting to upload for a long time, it might be quicker to store it and continue from the last line index then it will be to parse from the line where it's recieved each time


            Return True
        Catch ex As Exception
            WriteError(ex.Message, Err)
            Return False
        End Try
    End Function
    Public Function UpdateWorkUnits(lWU As List(Of clsWU)) As Boolean
        Try


            Return True
        Catch ex As Exception
            WriteError(ex.Message, Err)
            Return False
        Finally
            ''sqlitecon.close()
        End Try
    End Function
    Public Function ReCreditWorkUnits(lWU As List(Of clsWU)) As Boolean
        Try

            Return True
        Catch ex As Exception
            WriteError(ex.Message, Err)
            Return False
        Finally
            ''sqlitecon.close()
        End Try
    End Function
    Public ReadOnly Property RecreditAllWorkUnits As Boolean
        Get
            Try
                Dim rVal As Boolean = False
                Dim lWU As List(Of clsWU) = WorkUnits(" WHERE Credit=" & Chr(148) & Chr(148) & " AND ServerResponse LIKE '%WORK_ACK%' AND CoreStatus LIKE '%FINISHED_UNIT%'")
                Dim lRecredited As New List(Of clsWU)
                For Each wu In lWU
                    wu = AccreditWorkunit(wu)
                    If wu.Credit <> "" Then
                        rVal = True
                        lRecredited.Add(wu)
                    End If
                Next

                Using dbInstRecredit As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInstRecredit

                        If .conState = ConnectionState.Open Then
                            Dim bCommit As Boolean = Not .TransactionInProgress
                            If Not .TransactionInProgress Then
                                .BeginTransaction()
                            End If
                            For Each wu In lRecredited
                                sqlErr = .ExecuteNonQuery("UPDATE WorkUnits SET Credit='" & wu.Credit & "' PPD='" & wu.PPD & "' WHERE Client='" & wu.ClientName & "' Slot='" & wu.Slot & "' Downloaded='" & wu.dtDownloaded.ToString("s") & "' unit='" & wu.unit & "'")
                            Next
                            If bCommit Then .EndTransaction()
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
                Return rVal
            Catch ex As Exception
                WriteError(ex.Message, Err)
                Return False
            End Try
        End Get
    End Property
    'Public Overloads ReadOnly Property WorkUnits(dSelectQuery As Dictionary(Of String, String), Optional strSort As String = " ORDER BY Downloaded") As List(Of clsWU)
    '    'Use the dictionary to limit return values ( eg "Client" "test" would return only work units from test
    '    Get
    '        ''Clear lastSQL
    '        _LastSQL = String.Empty
    '        Dim lstWU As New List(Of clsWU)
    '        Try

    '            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
    '                With dbInst

    '                    If .conState = ConnectionState.Open Then
    '                        Dim bHas As Boolean
    '                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='WorkUnits'")
    '                            bHas = rdr.HasRows
    '                        End Using
    '                        If Not bHas Then Return lstWU
    '                        'Prepare query string
    '                        Dim qStr As New StringBuilder
    '                        qStr.Append("SELECT * FROM 'WorkUnits'")
    '                        If dSelectQuery.Count > 0 Then
    '                            For xInt As Int32 = 0 To dSelectQuery.Count - 1
    '                                If xInt = 0 Then
    '                                    qStr.Append(" WHERE " & dSelectQuery.Keys(xInt) & "='" & dSelectQuery.Values(xInt) & "'")
    '                                ElseIf xInt = dSelectQuery.Count - 1 Then
    '                                    qStr.Append(" AND " & dSelectQuery.Keys(xInt) & "='" & dSelectQuery.Values(xInt) & "'")
    '                                Else
    '                                    qStr.Append(" AND " & dSelectQuery.Keys(xInt) & "='" & dSelectQuery.Values(xInt) & "'")
    '                                End If
    '                            Next
    '                        End If
    '                        qStr.Append(strSort)

    '                        Using rdr As SQLiteDataReader = .dataReader(qStr.ToString)
    '                            While rdr.Read
    '                                Dim nWU As New clsWU
    '                                nWU.unit = CStr(rdr.Item("unit"))
    '                                nWU.Slot = CStr(rdr.Item("Slot"))
    '                                nWU.ClientName = FormatSQLString(CStr(rdr.Item("Client")), True)
    '                                nWU.PRCG = "Project:" & CStr(rdr.Item("Project")) & " " & CStr(rdr.Item("RCG"))
    '                                nWU.BoardType = CStr(rdr.Item("BoardType"))
    '                                nWU.Core = CStr(rdr.Item("core"))
    '                                nWU.CoreCompiler = CStr(rdr.Item("CoreCompiler"))
    '                                nWU.CoreSnippet = FormatSQLString(CStr(rdr.Item("CoreSnippet")), True)
    '                                nWU.CoreStatus = CStr(rdr.Item("CoreStatus"))
    '                                nWU.CoreVersion = CStr(rdr.Item("CoreVersion"))
    '                                nWU.Credit = CStr(rdr.Item("Credit"))
    '                                nWU.CS = CStr(rdr.Item("CS"))
    '                                nWU.WS = CStr(rdr.Item("WS"))
    '                                nWU.dtCompleted = CDate(rdr.Item("Completed"))
    '                                nWU.dtDownloaded = CDate(rdr.Item("Downloaded"))
    '                                nWU.dtStarted = CDate(rdr.Item("Started"))
    '                                nWU.dtSubmitted = CDate(rdr.Item("Submitted"))
    '                                nWU.HW = CStr(rdr.Item("HW"))
    '                                nWU.ServerResponce = CStr(rdr.Item("ServerResponse"))
    '                                nWU.PPD = CStr(rdr.Item("PPD"))
    '                                nWU.Credit = CStr(rdr.Item("Credit"))
    '                                nWU.UploadSize = CStr(rdr.Item("UploadSize"))
    '                                nWU.dtStartUpload = CDate(rdr.Item("StartUpload"))
    '                                lstWU.Add(nWU)
    '                            End While
    '                        End Using
    '                    Else
    '                        WriteDebug("Can't access the database, there should be more errors")
    '                    End If
    '                End With
    '            End Using
    '            'Add frames
    '            Try
    '                Using dbInstFrames As clsDB.clsDBInstance = dbPool.dbInstance
    '                    With dbInstFrames

    '                        If .conState = ConnectionState.Open Then
    '                            For Each nWU In lstWU
    '                                Dim bHas As Boolean = False
    '                                Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='FrameInfo_" & FormatSQLString(nWU.ClientName, True, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
    '                                    bHas = rdr.HasRows
    '                                End Using
    '                                If bHas Then
    '                                    Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'FrameInfo_" & FormatSQLString(nWU.ClientName, True, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
    '                                        While rdr.Read
    '                                            nWU.AddFrame("THIS IS A TEST (" & rdr.Item("Percentage").ToString & "%)", CDate(rdr.Item("dtPercentage")))
    '                                            Application.DoEvents()
    '                                        End While
    '                                    End Using
    '                                End If
    '                            Next
    '                        Else
    '                            WriteDebug("Can't access the database, there should be more errors")
    '                        End If
    '                    End With
    '                End Using
    '            Catch ex As Exception

    '            End Try
    '            'Add restart info
    '            Try
    '                Using dbInstRestart As clsDB.clsDBInstance = dbPool.dbInstance
    '                    With dbInstRestart

    '                        If .conState = ConnectionState.Open Then
    '                            For Each nWU In lstWU
    '                                Dim bHas As Boolean
    '                                Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='RestartInfo_" & FormatSQLString(nWU.ClientName, True, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
    '                                    bHas = rdr.HasRows
    '                                End Using
    '                                If bHas Then
    '                                    nWU.bHasRestarted = True
    '                                    Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'RestartInfo_" & FormatSQLString(nWU.ClientName, True, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
    '                                        While rdr.Read
    '                                            'THIS IS A TEST IS NEEDED FOR ACCURATE FRAME ADDING 
    '                                            nWU.restartInfo.AddRestart(CStr(rdr.Item("CoreStatus")), New clsWU.clsFrame("THIS IS A TEST (" & CStr(rdr.Item("Frame")) & "%)", CDate(rdr.Item("dtFrame"))))
    '                                        End While
    '                                    End Using
    '                                End If
    '                            Next
    '                        Else
    '                            WriteDebug("Can't access the database, there should be more errors")
    '                        End If
    '                    End With
    '                End Using
    '            Catch ex As Exception

    '            End Try
    '        Catch sqlEx As SQLiteException
    '            _LastSQL = sqlEx.Message
    '            WriteError(sqlEx.Message, Err)
    '        Catch ex As Exception
    '            WriteError(ex.Message, Err)
    '        End Try
    '        Return lstWU
    '    End Get
    'End Property
    Private _LastSQL As String = String.Empty
    Public ReadOnly Property LastSQL As String
        Get
            Return _LastSQL
        End Get
    End Property
    Public Overloads ReadOnly Property WorkUnits(sqlSelectString As String) As List(Of clsWU)
        Get
            ''Clear lastSQL
            _LastSQL = String.Empty
            Dim lstWU As New List(Of clsWU)
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Dim bHas As Boolean
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='WorkUnits'")
                                bHas = rdr.HasRows
                            End Using
                            If Not bHas Then Return lstWU
                            'Prepare query string
                            Dim qStr As New StringBuilder
                            qStr.Append("SELECT * FROM 'WorkUnits'")
                            Try
                                If sqlSelectString.Substring(0, 1) = " " Then
                                    qStr.Append(sqlSelectString)
                                Else
                                    qStr.Append(" " & sqlSelectString)
                                End If
                            Catch ex As Exception

                            End Try
                            If sqlSelectString = "" Then qStr.Append(" ORDER BY Downloaded DESC")
                            Using rdr As SQLiteDataReader = .dataReader(qStr.ToString)
                                While rdr.Read
                                    Dim nWU As New clsWU
                                    nWU.unit = CStr(rdr.Item("unit"))
                                    nWU.Slot = CStr(rdr.Item("Slot"))
                                    nWU.ClientName = CStr(rdr.Item("Client"))
                                    nWU.PRCG = "Project:" & CStr(rdr.Item("Project")) & " " & CStr(rdr.Item("RCG"))
                                    nWU.BoardType = CStr(rdr.Item("BoardType"))
                                    nWU.Core = CStr(rdr.Item("core"))
                                    nWU.CoreCompiler = CStr(rdr.Item("CoreCompiler"))
                                    nWU.CoreSnippet = FormatSQLString(CStr(rdr.Item("CoreSnippet")), True)
                                    nWU.CoreStatus = CStr(rdr.Item("CoreStatus"))
                                    nWU.CoreVersion = CStr(rdr.Item("CoreVersion"))
                                    nWU.Credit = CStr(rdr.Item("Credit"))
                                    nWU.CS = CStr(rdr.Item("CS"))
                                    nWU.WS = CStr(rdr.Item("WS"))
                                    nWU.dtCompleted = CDate(rdr.Item("Completed"))
                                    nWU.dtDownloaded = CDate(rdr.Item("Downloaded"))
                                    nWU.dtStarted = CDate(rdr.Item("Started"))
                                    nWU.dtSubmitted = CDate(rdr.Item("Submitted"))
                                    nWU.HW = CStr(rdr.Item("HW"))
                                    nWU.ServerResponce = CStr(rdr.Item("ServerResponse"))
                                    nWU.PPD = CStr(rdr.Item("PPD"))
                                    nWU.Credit = CStr(rdr.Item("Credit"))
                                    nWU.UploadSize = CStr(rdr.Item("UploadSize"))
                                    nWU.dtStartUpload = CDate(rdr.Item("StartUpload"))
                                    lstWU.Add(nWU)
                                End While
                            End Using
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
                'Add frames
                Try
                    Using dbInstFrames As clsDB.clsDBInstance = dbPool.dbInstance
                        With dbInstFrames

                            If .conState = ConnectionState.Open Then
                                For Each nWU In lstWU
                                    Dim bHas As Boolean = False
                                    Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='FrameInfo_" & nWU.ClientName & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                        bHas = rdr.HasRows
                                    End Using
                                    If bHas Then
                                        Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'FrameInfo_" & nWU.ClientName & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                            While rdr.Read
                                                nWU.AddFrame("THIS IS A TEST (" & rdr.Item("Percentage").ToString & "%)", CDate(rdr.Item("dtPercentage")))
                                                Application.DoEvents()
                                            End While
                                        End Using
                                    End If
                                Next
                            Else
                                WriteDebug("Can't access the database, there should be more errors")
                            End If
                        End With
                    End Using
                Catch ex As Exception

                End Try
                'Add restart info
                Try
                    Using dbInstRestart As clsDB.clsDBInstance = dbPool.dbInstance
                        With dbInstRestart

                            If .conState = ConnectionState.Open Then
                                For Each nWU In lstWU
                                    Dim bHas As Boolean
                                    Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='RestartInfo_" & nWU.ClientName & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                        bHas = rdr.HasRows
                                    End Using
                                    If bHas Then
                                        nWU.bHasRestarted = True
                                        Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'RestartInfo_" & nWU.ClientName & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                            While rdr.Read
                                                'THIS IS A TEST IS NEEDED FOR ACCURATE FRAME ADDING 
                                                nWU.restartInfo.AddRestart(CStr(rdr.Item("CoreStatus")), New clsWU.clsFrame("THIS IS A TEST (" & CStr(rdr.Item("Frame")) & "%)", CDate(rdr.Item("dtFrame"))))
                                            End While
                                        End Using
                                    End If
                                Next
                            Else
                                WriteDebug("Can't access the database, there should be more errors")
                            End If
                        End With
                    End Using
                Catch ex As Exception

                End Try
            Catch sqlEx As SQLiteException
                _LastSQL = sqlEx.Message
                WriteError(sqlEx.Message, Err)
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return lstWU
        End Get
    End Property
    
    Public ReadOnly Property WorkUnit(ClientName As String, SlotID As String, unit As String) As clsWU
        Get
            Dim nWU As New clsWU
            Try
                nWU.ClientName = ClientName
                nWU.Slot = SlotID
                nWU.unit = unit
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst

                        If .conState = ConnectionState.Open Then
                            Dim bHas As Boolean
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'WorkUnits' WHERE Client='" & FormatSQLString(ClientName, False, True) & "' AND Slot='" & SlotID & "' AND unit='" & unit & "'")
                                If rdr.Read Then
                                    bHas = True
                                    nWU.unit = CStr(rdr.Item("unit"))
                                    nWU.Slot = CStr(rdr.Item("Slot"))
                                    nWU.ClientName = CStr(rdr.Item("Client"))
                                    nWU.PRCG = "Project:" & CStr(rdr.Item("Project")) & " " & CStr(rdr.Item("RCG"))
                                    nWU.BoardType = CStr(rdr.Item("BoardType"))
                                    nWU.Core = CStr(rdr.Item("core"))
                                    nWU.CoreCompiler = CStr(rdr.Item("CoreCompiler"))
                                    nWU.CoreSnippet = FormatSQLString(CStr(rdr.Item("CoreSnippet")), True)
                                    nWU.CoreStatus = CStr(rdr.Item("CoreStatus"))
                                    nWU.CoreVersion = CStr(rdr.Item("CoreVersion"))
                                    nWU.Credit = CStr(rdr.Item("Credit"))
                                    nWU.CS = CStr(rdr.Item("CS"))
                                    nWU.WS = CStr(rdr.Item("WS"))
                                    nWU.dtCompleted = CDate(rdr.Item("Completed"))
                                    nWU.dtDownloaded = CDate(rdr.Item("Downloaded"))
                                    nWU.dtStarted = CDate(rdr.Item("Started"))
                                    nWU.dtSubmitted = CDate(rdr.Item("Submitted"))
                                    nWU.HW = CStr(rdr.Item("HW"))
                                    nWU.ServerResponce = CStr(rdr.Item("ServerResponse"))
                                    nWU.PPD = CStr(rdr.Item("PPD"))
                                    nWU.Credit = CStr(rdr.Item("Credit"))
                                    nWU.UploadSize = CStr(rdr.Item("UploadSize"))
                                    nWU.dtStartUpload = CDate(rdr.Item("StartUpload"))
                                Else
                                    Return nWU
                                End If
                            End Using
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                            Return nWU
                        End If
                    End With
                End Using

                'Add frames
                Using dbInstFrames As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInstFrames

                        If .conState = ConnectionState.Open Then
                            Dim bHas As Boolean
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='FrameInfo_" & FormatSQLString(nWU.ClientName, False, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                bHas = rdr.HasRows
                            End Using
                            If bHas Then
                                Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'FrameInfo_" & FormatSQLString(nWU.ClientName, False, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                    While rdr.Read
                                        nWU.AddFrame("THIS IS A TEST (" & rdr.Item("Percentage").ToString & "%)", CDate(rdr.Item("dtPercentage")))
                                        Application.DoEvents()
                                    End While
                                End Using
                            End If
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
                'Add restart info
                Using dbInstRestart As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInstRestart

                        If .conState = ConnectionState.Open Then
                            Dim bHas As Boolean = False
                            Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='RestartInfo_" & FormatSQLString(nWU.ClientName, False, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                bHas = rdr.HasRows
                            End Using
                            If bHas Then
                                nWU.bHasRestarted = True
                                Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'RestartInfo_" & FormatSQLString(nWU.ClientName, False, True) & "_" & nWU.Slot & "_" & nWU.unit & "'")
                                    While rdr.Read
                                        'THIS IS A TEST IS NEEDED FOR ACCURATE FRAME ADDING 
                                        nWU.restartInfo.AddRestart(CStr(rdr.Item("CoreStatus")), New clsWU.clsFrame("THIS IS A TEST (" & CStr(rdr.Item("Frame")) & "%)", CDate(rdr.Item("dtFrame"))))
                                    End While
                                End Using
                            End If
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return nWU
        End Get
    End Property
#End Region
#Region "EOC XML functions"
    Public Function InitEOC() As Boolean
        Dim bRet As Boolean = True
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        Dim bHas As Boolean = False
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='eoc_accounts'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            sqlErr = SQLiteErrorCode.Ok
                            sqlErr = .ExecuteNonQuery("CREATE TABLE eoc_accounts (username TEXT, team TEXT, eocid TEXT, signature TEXT, enabled TEXT)")
                            'If Not sqlErr = SQLiteErrorCode.Ok Then
                            '    bRet = False
                            '    WriteLog("Sqlite threw an error while trying to create the ExtremeOverclocking table", eSeverity.Important)
                            '    WriteLog(sqlErr.ToString, eSeverity.Critical)
                            '    Exit Try
                            'End If
                        End If
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='eocupdates'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            sqlErr = SQLiteErrorCode.Ok
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'eocupdates' (dtupdate DATETIME, username TEXT, teamid TEXT, teamname TEXT, teamrank TEXT, teamactiveusers TEXT, teamusers TEXT, teamchangerankday TEXT, teamchangerankweek TEXT, teampointsdayavg TEXT, teampointsupdate TEXT, teampointstoday TEXT, teampointsweek TEXT, teampoints TEXT, teamwus TEXT, userid TEXT, userteamrank TEXT, useroverallrank TEXT, userchangerankday TEXT, userchangerankweek TEXT, userpointsdayaverage TEXT, userpointsupdate TEXT, userpointstoday TEXT, userpointsweek TEXT, userpoints TEXT, userwus TEXT, strlastupdate TEXT, strunixtimestamp TEXT, updatestatus TEXT)")
                            'sqlErr = .ExecuteNonQuery("CREATE TABLE 'eocupdates' (dtUPDATE DATETIME, username TEXT, team TEXT, DATA blob)")
                            'If Not sqlErr = SQLiteErrorCode.Ok Then
                            '    bRet = False
                            '    WriteLog("Sqlite threw an error while trying to create the xmlupdates table", eSeverity.Important)
                            '    WriteLog(sqlErr.ToString, eSeverity.Critical)
                            '    Exit Try
                            'End If
                        End If

                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='signatureimage'")
                            bHas = rdr.HasRows
                        End Using
                        If Not bHas Then
                            sqlErr = SQLiteErrorCode.Ok
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'signatureimage' (dtUPDATE DATETIME, username TEXT, team TEXT, DATA blob)")
                            'If Not sqlErr = SQLiteErrorCode.Ok Then
                            '    bRet = False
                            '    WriteLog("Sqlite threw an error while trying to create the signatureimage table", eSeverity.Important)
                            '    WriteLog(sqlErr.ToString, eSeverity.Critical)
                            '    Exit Try
                            'End If
                        End If
                    Else
                        WriteLog("Can't access the database, there should be more errors", eSeverity.Important)
                        bRet = False
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
            bRet = False
        End Try
        Return bRet
    End Function
    '"CREATE TABLE 'ExtremeOverclocking' (name TEXT, team TEXT, eocid TEXT, signature TEXT, enabled TEXT)"
    Public Function EOCAccounts() As List(Of clsEOCInfo.sEOCAccount)
        Dim bRet As New List(Of clsEOCInfo.sEOCAccount)
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        Dim bHas As Boolean = False
                        Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'eoc_accounts'")
                            While rdr.Read
                                Dim nA As New clsEOCInfo.sEOCAccount
                                nA.Username = CStr(rdr.Item("username"))
                                nA.Teamnumber = CStr(rdr.Item("team"))
                                nA.ID = CStr(rdr.Item("eocid"))
                                nA.customIMG = CStr(rdr.Item("signature"))
                                nA.Enabled = CBool(CStr(rdr.Item("enabled")))
                                bRet.Add(nA)
                            End While
                        End Using
                    Else
                        WriteLog("Can't access the database, there should be more errors", eSeverity.Important)
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return bRet
    End Function
    Public ReadOnly Property HasAccount(UserName As String, TeamNumber As String) As Boolean
        Get
            Dim bRet As Boolean = False
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'eoc_accounts' WHERE username='" & UserName & "' AND team='" & TeamNumber & "'")
                                bRet = rdr.HasRows
                            End Using
                        Else
                            WriteLog("Can't access the database, there should be more errors", eSeverity.Important)
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return bRet
        End Get
    End Property
    Public Function SaveEOCAccounts(Accounts As List(Of clsEOCInfo.sEOCAccount)) As Boolean
        Dim bRet As Boolean = True
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        sqlErr = .ExecuteNonQuery("DELETE FROM 'eoc_accounts'")

                        Dim bCommit As Boolean = Not .TransactionInProgress
                        If Not .TransactionInProgress Then .BeginTransaction()
                        For Each Account As clsEOCInfo.sEOCAccount In Accounts
                            sqlErr = .ExecuteNonQuery("INSERT INTO 'eoc_accounts' (username, team, eocid, signature, enabled) VALUES('" & Account.Username & sib & Account.Teamnumber & sib & Account.ID & sib & Account.customIMG & sib & CStr(Account.Enabled) & "')")
                            'If Not sqlErr = SQLiteErrorCode.Ok Then
                            '    WriteLog("Sqlite threw an exception while inserting an account in the 'ExtremeOverclocking' table", eSeverity.Important)
                            '    WriteLog("Name: " & Account.Username & " team:" & Account.Teamnumber & " id:" & Account.ID & " signature:" & Account.customIMG & " enabled:" & CStr(Account.Enabled), eSeverity.Important)
                            '    WriteLog(sqlErr.ToString, eSeverity.Critical)
                            '    bRet = False
                            '    Exit Try
                            'End If
                        Next
                        If bCommit Then .EndTransaction()
                    Else
                        WriteLog("Can't access the database, there should be more errors", eSeverity.Important)
                        bRet = False
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
            bRet = False
        End Try
        Return bRet
    End Function
#Region "XML updates"
    '"CREATE TABLE 'eocupdates' (dtUPDATE DATETIME, user TEXT, team TEXT, DATA blob)"
    Public ReadOnly Property GetXMLUpdate(ByVal dtUpdate As DateTime, ByVal UserName As String, ByVal TeamNumber As String) As clsEOC.clsUpdates
        Get
            Dim xmlUpdate As New clsEOC.clsUpdates
            Try
                Dim mySelectQuery As String = "SELECT * FROM 'eocupdates' WHERE dtupdate='" & dtUpdate.ToString("s") & "' AND username='" & UserName & "' AND teamid='" & TeamNumber & "'"
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst

                        If .conState = ConnectionState.Open Then
                            Try
                                WriteDebug("Retrieving stored EOC update for " & UserName & "(" & TeamNumber & ") - " & dtUpdate.ToString("s"))
                                Using rdr As SQLiteDataReader = .dataReader(mySelectQuery)
                                    ' Always call Read before accessing data.
                                    While rdr.Read()
                                        '"CREATE TABLE 'eocupdates' (dtupdate DATETIME, username TEXT, teamid TEXT, teamname TEXT, teamrank TEXT, teamactiveusers TEXT, teamusers TEXT, teamchangerankday TEXT, teamchangerankweek TEXT, teampointsdayavg TEXT, teampointsupdate TEXT, teampointstoday TEXT, teampointsweek TEXT, teampoints TEXT, teamwus TEXT, userid TEXT, userteamrank TEXT, useroverallrank TEXT, userchangerankday TEXT, userchangerankweek TEXT, userpointsdayaverage TEXT, userpointsupdate TEXT, userpointstoday TEXT, userpointsweek TEXT, userpoints TEXT, userwus TEXT, strlastupdate TEXT, strunixtimestamp TEXT, updatestatus TEXT)"
                                        xmlUpdate.Update.User.User_Name = UserName
                                        xmlUpdate.Update.Team.TeamID = TeamNumber
                                        xmlUpdate.Update.User.Change_Rank_24h = CStr(rdr.Item("userchangerankday"))
                                        xmlUpdate.Update.User.Change_Rank_7days = CStr(rdr.Item("userchangerankweek"))
                                        xmlUpdate.Update.User.Overall_Rank = CStr(rdr.Item("useroverallrank"))
                                        xmlUpdate.Update.User.Points = CStr(rdr.Item("userpoints"))
                                        xmlUpdate.Update.User.Points_24h_Avg = CStr(rdr.Item("userpointsdayaverage"))
                                        xmlUpdate.Update.User.Points_Today = CStr(rdr.Item("userpointstoday"))
                                        xmlUpdate.Update.User.Points_Update = CStr(rdr.Item("userpointsupdate"))
                                        xmlUpdate.Update.User.Points_Week = CStr(rdr.Item("userpointsweek"))
                                        xmlUpdate.Update.User.Team_Rank = CStr(rdr.Item("userteamrank"))
                                        xmlUpdate.Update.User.UserID = CStr(rdr.Item("userid"))
                                        xmlUpdate.Update.User.WUs = CStr(rdr.Item("userwus"))
                                        xmlUpdate.Update.Team.Change_Rank_24h = CStr(rdr.Item("teamchangerankday"))
                                        xmlUpdate.Update.Team.Change_Rank_7days = CStr(rdr.Item("teamchangerankweek"))
                                        xmlUpdate.Update.Team.Points = CStr(rdr.Item("teampoints"))
                                        xmlUpdate.Update.Team.TeamName = CStr(rdr.Item("teamname"))
                                        xmlUpdate.Update.Team.Points_24h_Avg = CStr(rdr.Item("teampointsdayaverage"))
                                        xmlUpdate.Update.Team.Points_Today = CStr(rdr.Item("teampointstoday"))
                                        xmlUpdate.Update.Team.Points_Update = CStr(rdr.Item("teampointsupdate"))
                                        xmlUpdate.Update.Team.Points_Week = CStr(rdr.Item("teampointsweek"))
                                        xmlUpdate.Update.Team.Rank = CStr(rdr.Item("teamrank"))
                                        xmlUpdate.Update.Team.Users = CStr(rdr.Item("teamusers"))
                                        xmlUpdate.Update.Team.Users_Active = CStr(rdr.Item("teamactiveusers"))
                                        xmlUpdate.Update.Team.WUs = CStr(rdr.Item("teamwus"))
                                        xmlUpdate.Update.UpdateStatus.dtUpdate = CDate(rdr.Item("dtupdate"))
                                        xmlUpdate.Update.UpdateStatus.strLastUpdate = CStr(rdr.Item("strlastupdate"))
                                        xmlUpdate.Update.UpdateStatus.strUnixTimeStamp = CStr(rdr.Item("strunixtimestamp"))
                                        xmlUpdate.Update.UpdateStatus.Update_Status = CStr(rdr.Item("updatestatus"))
                                    End While
                                End Using
                            Catch ex As Exception
                                WriteError(ex.Message, Err)
                            End Try
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
                xmlUpdate = New clsEOC.clsUpdates
            End Try
            Return xmlUpdate
        End Get
    End Property
    Public ReadOnly Property lasteocupdate(UserName As String, Teamnumber As String) As clsEOC.clsUpdates
        Get
            Dim xmlUpdate As New clsEOC.clsUpdates
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst

                        If .conState = ConnectionState.Open Then
                            Try
                                Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'eocupdates' WHERE username='" & UserName & "' AND teamid='" & Teamnumber & "' ORDER BY dtupdate DESC")
                                    ' Always call Read before accessing data.
                                    If Not rdr.HasRows Then Exit Try
                                    If rdr.Read Then
                                        xmlUpdate.Update.User.User_Name = UserName
                                        xmlUpdate.Update.Team.TeamID = Teamnumber
                                        xmlUpdate.Update.User.Change_Rank_24h = CStr(rdr.Item("userchangerankday"))
                                        xmlUpdate.Update.User.Change_Rank_7days = CStr(rdr.Item("userchangerankweek"))
                                        xmlUpdate.Update.User.Overall_Rank = CStr(rdr.Item("useroverallrank"))
                                        xmlUpdate.Update.User.Points = CStr(rdr.Item("userpoints"))
                                        xmlUpdate.Update.User.Points_24h_Avg = CStr(rdr.Item("userpointsdayaverage"))
                                        xmlUpdate.Update.User.Points_Today = CStr(rdr.Item("userpointstoday"))
                                        xmlUpdate.Update.User.Points_Update = CStr(rdr.Item("userpointsupdate"))
                                        xmlUpdate.Update.User.Points_Week = CStr(rdr.Item("userpointsweek"))
                                        xmlUpdate.Update.User.Team_Rank = CStr(rdr.Item("userteamrank"))
                                        xmlUpdate.Update.User.UserID = CStr(rdr.Item("userid"))
                                        xmlUpdate.Update.User.WUs = CStr(rdr.Item("userwus"))
                                        xmlUpdate.Update.Team.Change_Rank_24h = CStr(rdr.Item("teamchangerankday"))
                                        xmlUpdate.Update.Team.Change_Rank_7days = CStr(rdr.Item("teamchangerankweek"))
                                        xmlUpdate.Update.Team.Points = CStr(rdr.Item("teampoints"))
                                        xmlUpdate.Update.Team.TeamName = CStr(rdr.Item("teamname"))
                                        xmlUpdate.Update.Team.Points_24h_Avg = CStr(rdr.Item("teampointsdayaverage"))
                                        xmlUpdate.Update.Team.Points_Today = CStr(rdr.Item("teampointstoday"))
                                        xmlUpdate.Update.Team.Points_Update = CStr(rdr.Item("teampointsupdate"))
                                        xmlUpdate.Update.Team.Points_Week = CStr(rdr.Item("teampointsweek"))
                                        xmlUpdate.Update.Team.Rank = CStr(rdr.Item("teamrank"))
                                        xmlUpdate.Update.Team.Users = CStr(rdr.Item("teamusers"))
                                        xmlUpdate.Update.Team.Users_Active = CStr(rdr.Item("teamactiveusers"))
                                        xmlUpdate.Update.Team.WUs = CStr(rdr.Item("teamwus"))
                                        xmlUpdate.Update.UpdateStatus.dtUpdate = CDate(rdr.Item("dtupdate"))
                                        xmlUpdate.Update.UpdateStatus.strLastUpdate = CStr(rdr.Item("strlastupdate"))
                                        xmlUpdate.Update.UpdateStatus.strUnixTimeStamp = CStr(rdr.Item("strunixtimestamp"))
                                        xmlUpdate.Update.UpdateStatus.Update_Status = CStr(rdr.Item("updatestatus"))
                                    End If
                                End Using
                            Catch ex As Exception
                                'WriteError(ex.Message, Err)
                            End Try
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return xmlUpdate
        End Get
    End Property
    Public ReadOnly Property EOC_dtLastUpdate(UserName As String, Teamnumber As String) As DateTime
        Get
            Dim dtRet As DateTime = #1/1/2000#
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst

                        If .conState = ConnectionState.Open Then
                            Using sqReader As SQLiteDataReader = .dataReader("SELECT dtupdate FROM 'eocupdates' WHERE username='" & UserName & "' AND teamid='" & Teamnumber & "' ORDER BY sigDate DESC")
                                ' Always call Read before accessing data.
                                If Not sqReader.HasRows Then Exit Try
                                sqReader.Read()
                                dtRet = CDate(sqReader.Item("dtupdate"))
                            End Using
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return dtRet
        End Get
    End Property
    Public Function GetXMLUpdateTimeStamps(ByVal UserName As String, ByVal TeamNumber As String) As List(Of DateTime)
        Dim lSigDate As New List(Of DateTime)
        Try
            Dim mySelectQuery As String = "SELECT dtupdate FROM 'eocupdates' WHERE username='" & UserName & "' AND teamid='" & TeamNumber & "' ORDER BY dtupdate DESC"
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Using sqReader As SQLiteDataReader = .dataReader(mySelectQuery)
                            ' Always call Read before accessing data.
                            While sqReader.Read()
                                lSigDate.Add(CDate(sqReader.Item("dtupdate")))
                            End While
                        End Using
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return lSigDate
    End Function
    Public Function InsertXMLUpdate(ByVal NewUpdate As clsEOC.clsUpdates) As Boolean
        Dim bRet As Boolean = False
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        '"CREATE TABLE 'eocupdates' (dtupdate DATETIME, username TEXT, teamid TEXT, teamname TEXT, teamrank TEXT, teamactiveusers TEXT, teamusers TEXT, teamchangerankday TEXT, teamchangerankweek TEXT, teampointsdayavg TEXT, teampointsupdate TEXT, teampointstoday TEXT, teampointsweek TEXT, teampoints TEXT, teamwus TEXT, userid TEXT, userteamrank TEXT, useroverallrank TEXT, userchangerankday TEXT, userchangerankweek TEXT, userpointsdayaverage TEXT, userpointsupdate TEXT, userpointstoday TEXT, userpointsweek TEXT, userpoints TEXT, userwus TEXT, strlastupdate TEXT, strunixtimestamp TEXT, updatestatus TEXT)"
                        Dim sQuery As String = "INSERT INTO 'eocupdates' (dtupdate, username, teamid, teamname, teamrank, teamactiveusers, teamusers, teamchangerankday, teamchangerankweek, teampointsdayavg, teampointsupdate, teampointstoday, teampointsweek, teampoints, teamwus, userid, userteamrank, useroverallrank, userchangerankday, userchangerankweek, userpointsdayaverage, userpointsupdate, userpointstoday, userpointsweek, userpoints, userwus, strlastupdate, strunixtimestamp, updatestatus) VALUES('" & NewUpdate.Last_Update_LocalTime.ToString("s") & sib & NewUpdate.Update.User.User_Name & sib & NewUpdate.Update.Team.TeamID & sib & NewUpdate.Update.Team.TeamName & sib & NewUpdate.Update.Team.Rank & sib & NewUpdate.Update.Team.Users_Active & sib & NewUpdate.Update.Team.Users & sib & NewUpdate.Update.Team.Change_Rank_24h & sib & NewUpdate.Update.Team.Change_Rank_7days & sib & NewUpdate.Update.Team.Points_24h_Avg & sib & NewUpdate.Update.Team.Points_Update & sib & NewUpdate.Update.Team.Points_Today & sib & NewUpdate.Update.Team.Points_Week & sib & NewUpdate.Update.Team.Points & sib & NewUpdate.Update.Team.WUs & sib & NewUpdate.Update.User.UserID & sib & NewUpdate.Update.User.Team_Rank & sib & NewUpdate.Update.User.Overall_Rank & sib & NewUpdate.Update.User.Change_Rank_24h & sib & NewUpdate.Update.User.Change_Rank_7days & sib & NewUpdate.Update.User.Points_24h_Avg & sib & NewUpdate.Update.User.Points_Update & sib & NewUpdate.Update.User.Points_Today & sib & NewUpdate.Update.User.Points_Week & sib & NewUpdate.Update.User.Points & sib & NewUpdate.Update.User.WUs & sib & NewUpdate.Update.UpdateStatus.strLastUpdate & sib & NewUpdate.Update.UpdateStatus.strUnixTimeStamp & sib & NewUpdate.Update.UpdateStatus.Update_Status & "')"
                        sqlErr = SQLiteErrorCode.Ok
                        sqlErr = .ExecuteNonQuery(sQuery)
                        'Dim bf As New BinaryFormatter()
                        'Dim ms As New MemoryStream()
                        'bf.Serialize(ms, NewUpdate)
                        'Dim bCommit As Boolean = Not .TransactionInProgress
                        'If Not .TransactionInProgress Then
                        '    .BeginTransaction()
                        'End If
                        'Dim EOCcmd As SQLiteCommand = .GetSQLCommand
                        'EOCcmd.CommandText = "INSERT INTO 'eocupdates' (dtUpdate, username, team, DATA) VALUES ('" & NewUpdate.Last_Update_LocalTime.ToString("s") & sib & NewUpdate.Update.User.User_Name & sib & NewUpdate.Update.Team.TeamID & "'.@xmldata)"
                        'Dim XML() As Byte = ms.ToArray
                        'Dim SQLparm As New SQLiteParameter("@xmldata", XML)
                        'SQLparm.DbType = DbType.Binary
                        'SQLparm.Value = XML
                        'EOCcmd.Parameters.Add(SQLparm)
                        'sqlErr = .ExecuteCommand(EOCcmd)
                        'If Not sqlErr = SQLiteErrorCode.Ok Then
                        '    WriteLog("Sqlite threw an exception trying to store an eoc xml update", eSeverity.Important)
                        '    WriteLog(sqlErr.ToString, eSeverity.Critical)
                        '    bRet = False
                        '    Try
                        '        If bCommit Then .EndTransaction()
                        '    Catch ex As Exception
                        '        WriteDebug("Trying to commit  a transaction after an error occured resulted in an exception")
                        '        WriteDebug(ex.Message)
                        '    End Try
                        '    Exit Try
                        'End If
                        WriteLog("Stored EOC update " & NewUpdate.Last_Update_LocalTime.ToString("s") & " - " & NewUpdate.Update.User.User_Name & "(" & NewUpdate.Update.Team.TeamName & ")")
                        'If bCommit Then .EndTransaction()
                        bRet = True
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError("clsStatistics_InsertXMLUpdate", Err)
        End Try
        Return bRet
    End Function
    Public Function GetAllXMLUpdates(ByVal UserName As String, ByVal TeamNumber As String) As List(Of clsEOC.clsUpdates)
        Dim alUpdates As New List(Of clsEOC.clsUpdates)
        Dim ts As New List(Of DateTime)
        Try
            ts = GetXMLUpdateTimeStamps(UserName, TeamNumber)
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Try
            For xInt As Int32 = 0 To ts.Count - 1
                alUpdates.Add(GetXMLUpdate(ts(xInt), UserName, TeamNumber))
            Next
            Return alUpdates
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return alUpdates
    End Function
#End Region
#Region "SignatureImage"
    Private Function BlobToImage(ByVal blob As Object) As Bitmap
        Try
            Dim mStream As New System.IO.MemoryStream
            Dim pData() As Byte = DirectCast(blob, Byte())
            mStream.Write(pData, 0, Convert.ToInt32(pData.Length))
            Dim bm As Bitmap = New Bitmap(mStream, False)
            mStream.Dispose()
            Return bm
        Catch ex As Exception
            WriteError("BlobToImage", Err)
            Return Nothing
        End Try
    End Function
    '"CREATE TABLE 'signatureimage' (dtUPDATE DATETIME, user TEXT, team, TEXT, DATA blob)"
    Public ReadOnly Property signatureDTs(UserName As String, TeamNumber As String) As List(Of DateTime)
        Get
            Dim rVal As New List(Of DateTime)
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Using SQLreader As SQLiteDataReader = .dataReader("SELECT dtUPDATE FROM 'signatureimage' WHERE username='" & UserName & "' AND team='" & TeamNumber & "' ORDER BY dtUPDATE DESC")
                                While SQLreader.Read
                                    rVal.Add(CDate(CStr(SQLreader.Item("sigDate"))))
                                End While
                            End Using
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return rVal
        End Get
    End Property
    Public ReadOnly Property signatureImages(UserName As String, TeamNumber As String) As List(Of Image)
        Get
            Dim rVal As New List(Of Image)
            Try
                Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                    With dbInst
                        If .conState = ConnectionState.Open Then
                            Using SQLreader As SQLiteDataReader = .dataReader("SELECT DATA FROM 'signatureimage' WHERE username='" & UserName & "' AND team='" & TeamNumber & "' ORDER BY dtUPDATE DESC")
                                SQLreader.Read()
                                rVal.Add(BlobToImage(SQLreader("DATA")))
                            End Using
                        Else
                            WriteDebug("Can't access the database, there should be more errors")
                        End If
                    End With
                End Using
            Catch ex As Exception
                WriteError(ex.Message, Err)
            End Try
            Return rVal
        End Get
    End Property
    Public Function GetSIGImage(ByVal UserName As String, ByVal TeamNumber As String, Optional dtSignature As DateTime = #1/1/2000#) As Image
        Dim tImg As Image = Nothing
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst
                    If .conState = ConnectionState.Open Then
                        If dtSignature = #1/1/2000# Then
                            Using SQLreader As SQLiteDataReader = .dataReader("SELECT * FROM 'signatureimage' WHERE username='" & UserName & "' AND team='" & TeamNumber & "' ORDER BY dtUPDATE DESC")
                                SQLreader.Read()
                                tImg = BlobToImage(SQLreader("DATA"))
                            End Using
                        Else
                            Using SQLreader As SQLiteDataReader = .dataReader("SELECT * FROM 'signatureimage' WHERE username='" & UserName & "' AND team='" & TeamNumber & "' AND dtUPDATE='" & dtSignature.ToString("s") & "'")
                                SQLreader.Read()
                                tImg = BlobToImage(SQLreader("DATA"))
                            End Using
                        End If
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError("clsStatistics_GetSIGImage", Err)
        End Try
        Return tImg
    End Function
    Public Function SaveSIGImage(ByVal SigDate As DateTime, ByVal UserName As String, ByVal TeamNumber As String, ByVal bArr() As Byte) As Boolean
        Dim bRet As Boolean = False
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Dim EOCcmd As SQLiteCommand = .GetSQLCommand
                        EOCcmd.CommandText = "INSERT INTO 'signatureimage' (dtUPDATE, username, team, DATA) VALUES ('" & SigDate.ToString("s") & "','" & UserName & "','" & TeamNumber & "',@image)"
                        Dim SQLparm As New SQLiteParameter("@image")
                        SQLparm.DbType = DbType.Binary
                        SQLparm.Value = bArr
                        EOCcmd.Parameters.Add(SQLparm)
                        Try
                            EOCcmd.ExecuteNonQuery()
                        Catch ex As Exception
                            bRet = False
                        End Try
                        Try
                            EOCcmd.ExecuteNonQuery()
                            bRet = True
                        Catch ex As Exception
                            bRet = False
                        End Try
                        'sqlErr = .ExecuteCommand(EOCcmd)
                        'bRet = (sqlErr = SQLiteErrorCode.Ok)
                        'If sqlErr <> SQLiteErrorCode.Ok Then
                        '    WriteLog("Sqlite threw an exception while trying to insert a signature image - " & sqlErr.ToString, eSeverity.Critical)
                        'End If
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError("SaveSigImage", Err)
            Return False
        End Try
        Return bRet
    End Function
#End Region

#End Region
#Region "Settings"
    'Store options in db
    Public Function StoreSettings(ByVal Settings As Dictionary(Of String, String)) As Boolean
        Dim bRet As Boolean = False
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Dim bCommit As Boolean = Not .TransactionInProgress
                        If Not .TransactionInProgress Then
                            .BeginTransaction()
                        End If
                        Dim bR As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='Settings'")
                            bR = rdr.HasRows
                        End Using
                        If Not bR Then
                            'Create table
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'Settings' (Setting TEXT, Value TEXT)")
                        Else
                            'Check for stored settings
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM Settings")
                                bR = rdr.HasRows
                            End Using
                            If bR Then
                                'Clear table
                                sqlErr = .ExecuteNonQuery("DELETE FROM Settings")
                            End If
                        End If
                        .BeginTransaction()
                        For xInt As Int32 = 0 To Settings.Count - 1
                            sqlErr = .ExecuteNonQuery("INSERT INTO Settings (Setting, Value) VALUES ('" & Settings.Keys(xInt).ToString & "','" & Settings.Values(xInt).ToString & "')")
                        Next
                        If bCommit Then .EndTransaction()
                        bRet = True
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
            bRet = False
        End Try
        Return bRet
    End Function
    Public Function ReadSettings() As Dictionary(Of String, String)
        Dim rVal As New Dictionary(Of String, String)
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Dim bR As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='Settings'")
                            bR = rdr.HasRows
                        End Using
                        If Not bR Then
                            'Create table
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'Settings' (Setting TEXT, Value TEXT)")
                        Else
                            'Read table
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'Settings'")
                                While rdr.Read
                                    If Not rVal.ContainsKey(CStr(rdr.Item(0))) Then
                                        rVal.Add(CStr(rdr.Item(0)), CStr(rdr.Item(1)))
                                    End If
                                End While
                            End Using
                        End If
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return rVal
    End Function
    Public Function HasSettings() As Boolean
        Dim bRet As Boolean = False
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name FROM sqlite_master WHERE name='Settings'")
                            bRet = rdr.HasRows
                        End Using
                        If bRet Then
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'Settings'")
                                bRet = rdr.HasRows
                            End Using
                        End If
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
            bRet = False
        End Try
        Return bRet
    End Function
#End Region
#Region "Remote clients"
    Public Function HasRemoteClients() As Boolean
        Dim bRet As Boolean = False
        Try
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name from sqlite_master WHERE name='RemoteClients'")
                            bRet = rdr.HasRows
                        End Using
                        If bRet Then
                            Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'RemoteClients'")
                                bRet = rdr.HasRows
                            End Using
                        End If
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
            bRet = False
        End Try
        Return bRet
    End Function
    Public Function SaveRemoteClients() As Boolean
        Dim bRet As Boolean = False
        Try
            If IsNothing(logParser) Then
                WriteLog("Attempt to save remote clients when none are specified.", eSeverity.Debug)
                Exit Try
            ElseIf logParser.Clients.Count <= 1 Then
                WriteLog("Attempt to save remote clients when none are specified.", eSeverity.Debug)
                Exit Try
            End If
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        '.BeginTransaction()
                        Dim b As Boolean
                        Using rdr As SQLiteDataReader = .dataReader("SELECT name from sqlite_master WHERE name='RemoteClients'")
                            b = rdr.HasRows
                        End Using
                        If Not b Then
                            sqlErr = .ExecuteNonQuery("CREATE TABLE 'RemoteClients' (ClientName TEXT, ClientLocation TEXT, FCPort TEXT, PWD TEXT, FWPort TEXT, Enabled TEXT)")
                        Else
                            sqlErr = .ExecuteNonQuery("DELETE FROM RemoteClients")
                        End If
                        If logParser.Clients.Count = 0 Then Return True
                        For xInt As Int32 = 1 To logParser.Clients.Count - 1
                            sqlErr = .ExecuteNonQuery("INSERT INTO 'RemoteClients' (ClientName, ClientLocation, FCPort, PWD, FWPort, Enabled) VALUES ('" & FormatSQLString(logParser.Clients(xInt).ClientName, False, True) & "','" & logParser.Clients(xInt).ClientLocation & "','" & logParser.Clients(xInt).FCPort & "','" & logParser.Clients(xInt).PWD & "','" & logParser.Clients(xInt).FWPort & "','" & logParser.Clients(xInt).Enabled.ToString & "')")
                        Next
                        '.EndTransaction()
                        bRet = True
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
            bRet = False
        End Try
        Return bRet
    End Function
    Public Function ReadRemoteClients() As Boolean
        'Should only be called AFTER initializing local client!
        If Not HasRemoteClients() Then Return False
        Dim bRet As Boolean = False
        Try
            If logParser.Clients.Count = 0 Then
                WriteLog("Attempt to read remote clients before initializing the local client", eSeverity.Debug)
                Exit Try
            End If
            Using dbInst As clsDB.clsDBInstance = dbPool.dbInstance
                With dbInst

                    If .conState = ConnectionState.Open Then
                        Dim iOld As Int32 = logParser.Clients.Count
                        Using rdr As SQLiteDataReader = .dataReader("SELECT * FROM 'RemoteClients'")
                            While rdr.Read
                                logParser.AddClient(FormatSQLString(CStr(rdr.Item(0)), True, True), CStr(rdr.Item(1)), CStr(rdr.Item(2)), CStr(rdr.Item(3)), CStr(rdr.Item(4)), CBool(rdr.Item(5)))
                            End While
                        End Using
                        bRet = logParser.Clients.Count > iOld
                    Else
                        WriteDebug("Can't access the database, there should be more errors")
                    End If
                End With
            End Using
        Catch ex As Exception
            WriteError(ex.Message, Err)
        End Try
        Return bRet
    End Function
#End Region
#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: dispose managed state (managed objects).
                dbPool.Dispose()
            End If

            ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
            ' TODO: set large fields to null.
        End If
        Me.disposedValue = True
    End Sub

    ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
    'Protected Overrides Sub Finalize()
    '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
    '    Dispose(False)
    '    MyBase.Finalize()
    'End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region
End Class


